Click here to Skip to main content
15,921,276 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
May be its repeated but I dint get the solution anywhere.

I want to split an excel file into multiple sheets or multiple files.
I got some code on VBA but I strictly need it in c# window application. I am unable to convert the VBA code to c#.

I have file like:

Name Month Salary
[A] Jan 50k
[B] Jan 55k
[A] Feb 45K
[B] Feb 60K

I want to create separate sheet for each name like:

Name Month Salary
[A] Jan 50k
[A] Feb 45K


Name Month Salary
[B] Jan 55k
[B] Feb 60K

I want two separate files. One for Name [A] and other for Name [B] having their corresponding rows only on each file.

What I have tried:

The code I am working with in VBA. But some classes are different in C#. So I am unable to implement it in C#

Option Explicit
Sub SplitIntoSeperateFiles()

Dim OutBook As Workbook
Dim DataSheet As Worksheet, OutSheet As Worksheet
Dim FilterRange As Range
Dim UniqueNames As New Collection
Dim LastRow As Long, LastCol As Long, _
    NameCol As Long, Index As Long
Dim OutName As String

'set references and variables up-front for ease-of-use
Set DataSheet = ThisWorkbook.Worksheets("Sheet1")
NameCol = 1
LastRow = DataSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = DataSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set FilterRange = Range(DataSheet.Cells(1, NameCol), DataSheet.Cells(LastRow, LastCol))

'loop through the name column and store unique names in a collection
For Index = 2 To LastRow
    On Error Resume Next
        UniqueNames.Add Item:=DataSheet.Cells(Index, NameCol), Key:=DataSheet.Cells(Index, NameCol)
    On Error GoTo 0
Next Index

'iterate through the unique names collection, writing 
'to new workbooks and saving as the group name .xls
Application.DisplayAlerts = False
For Index = 1 To UniqueNames.Count
    Set OutBook = Workbooks.Add
    Set OutSheet = OutBook.Sheets(1)
    With FilterRange
        .AutoFilter Field:=NameCol, Criteria1:=UniqueNames(Index)
        .SpecialCells(xlCellTypeVisible).Copy OutSheet.Range("A1")
    End With
    OutName = ThisWorkbook.FullName
    OutName = Left(OutName, InStrRev(OutName, "\"))
    OutName = OutName & UniqueNames(Index)
    OutBook.SaveAs Filename:=OutName, FileFormat:=xlExcel8
    OutBook.Close SaveChanges:=False
    Call ClearAllFilters(DataSheet)
Next Index
Application.DisplayAlerts = True

End Sub

'safely clear all the filters on data sheet
Sub ClearAllFilters(TargetSheet As Worksheet)
    With TargetSheet
        TargetSheet.AutoFilterMode = False
        If .FilterMode Then
        End If
    End With
End Sub
Updated 12-Apr-17 15:02pm
[no name] 12-Apr-17 13:57pm    
And what? Do you think we are going to code this up for you based on a vague description and unknown business rules? Not going to happen.
Bhola Ram Sahu 12-Apr-17 14:02pm    
Thanks for your kind reply. I know you will not do this for me. Please dont waste your time on such comments.

There are still good people who can suggest me any link.
[no name] 12-Apr-17 14:10pm    
Well since you didn't bother asking a question or describing an actual problem why are you wasting our time?

I would suggest
Bhola Ram Sahu 12-Apr-17 21:05pm
[no name] 12-Apr-17 21:31pm    
And so what does that not-a-legitimate-question on SO have to do with your not-a-legitimate-question?

Even with best will, it is impossible to give you the answer you want.
I want to split an excel file into multiple sheets or multiple files.

This is the only part that explain what you want, but it is no way near something usable to make the program you want, so any work on the question is a waste of time.

For now, the best explanation you can do is publish the source code you have in VBA.
I am totally blind about the logic

The logic of the C# will be exactly the same as VBA.
since you don't get the logic, I suspect you are not a programmer.
Programmers spend a awful lot of time studying to learn all necessary techniques and acquire the necessary knowledge. That is why people like you, that have no time or no <ill to learn programming, pay them to do the job.

Your question fit in category "do my job/homework for free", that why you will have greatest difficulties to find someone to it for free.
My best advice: hire a programmer.
Share this answer
You could do something like this in CopyData. Send the filename to CopyData void CopyData(string strFile). Then after range.Copy add the following lines.

Excel.Range range_name = sheetDest.get_Range(string.Format("E{0}", _currentRowCount), string.Format("E{0}", _currentRowCount + sheetRowCount));
range_name.Value = strFile;
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900