Convert excel sheet

Hello,
I have code which saves the excel sheet to .csv.
It works well with 1 sheet but how do  address multiple sheets and save the seperate csv for every sheet.
Please find the code for 1  sheet below:

  Private Sub SurroundingSub()
        Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")

        If officeType Is Nothing Then
        Else
            Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
            app.DisplayAlerts = False
            Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
            Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration.csv"
            excelWorkbook.SaveAs(newFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
            excelWorkbook.Close()
            app.Quit()
        End If
    End Sub

Open in new window

RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
A csv file can only have one sheet.

If this was VBA I would Loop through each sheet in the workbook, do the save for each sheet  and updating the name for each sheet.
0
RIASAuthor Commented:
Roy,
Yes, but what i really need is: My excel workbook has many sheets. When I am saving it in .csv format it should save individual .csv file for every sheet in excel.
Thanks
0
Roy CoxGroup Finance ManagerCommented:
Which is what looping through the sheets will do. Something like

 Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")
    Dim WS As Excel.Worksheet
    If officeType Is Nothing Then
    Else
        Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        app.DisplayAlerts = False
        Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
        Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration"
        For Each WS In ThisWorkbook.Worksheets
            Sheets(WS.Name).Copy
            excelWorkbook.SaveAs FileName:=SaveToDirectory & excelWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
        Next WS

        excelWorkbook.Close()
        app.Quit()
    End If

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RIASAuthor Commented:
Thanks Roy, Will try and brb
0
Roy CoxGroup Finance ManagerCommented:
Please note I just made a slight change to the original code

excelWorkbook.SaveAs FileName:=SaveToDirectory & excelWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV

Open in new window

0
RIASAuthor Commented:
Hi Roy,
it errors at
   Sheets(WS.Name).Copy
0
Roy CoxGroup Finance ManagerCommented:
Try

excelWorkbook.Sheets(WS.Name).Copy

Open in new window

0
RIASAuthor Commented:
txs ...trying
0
RIASAuthor Commented:
Roy,
Same sheet gets saved with all different sheet names.
0
RIASAuthor Commented:
        Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")
        Dim WS As Excel.Worksheet
        If officeType Is Nothing Then
        Else
            Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
            app.DisplayAlerts = False
            Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
            Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration"

            For Each WS In excelWorkbook.Worksheets
                Try
                    excelWorkbook.Sheets(WS.Name).Copy

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                excelWorkbook.SaveAs(newFileName & "-" & WS.Name & ".csv", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)



            Next WS

            excelWorkbook.Close()
            app.Quit()
        End If

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
I've amended it some more

 Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")
    Dim WS As Excel.Worksheet
    If officeType Is Nothing Then
    Else
        Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        app.DisplayAlerts = False
        Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
        Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration"
        With excelWorkbook
            For Each WS In .Worksheets
               ws.Copy
                ws.SaveAs FileName:=SaveToDirectory & newFileName & "-" & .WS.Name & ".csv", FileFormat:=xlCSV
            Next WS
            .Close()
            app.Quit()
        End If

Open in new window

0
RIASAuthor Commented:
thanks...trying
0
RIASAuthor Commented:
Hi Roy,
After 4 loops it gave me this error:

'Exception from HRESULT: 0x800A03EC'
0
Roy CoxGroup Finance ManagerCommented:
I believe that error is caused by many things, maybe the sheet name that it is working on causes a problm when naming the csv.

Can you attach an the excel workbook
0
RIASAuthor Commented:
Can you suggest on how do I count the number of active sheets in the excel .
There are some in the sheets hidden.
0
RIASAuthor Commented:
Can you suggest on how to the active worksheets count on the workbook.
0
Roy CoxGroup Finance ManagerCommented:
The hidden sheets could be causing the problem.

This should give you a message with the sheets count, also the code should only work on visible sheets

Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")
Dim WS As Excel.Worksheet
If officeType Is Nothing Then
Else
    Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    app.DisplayAlerts = False
    Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
    Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration"
    With excelWorkbook
        MsgBox .Worksheets.Count
        For Each WS In .Worksheets
            If WS.Visible = xlSheetVisible Then
                WS.Copy
                WS.SaveAs Filename:=SaveToDirectory & newFileName & "-" & .WS.Name & ".csv", FileFormat:=xlCSV
            End If
        Next WS
        .Close()
        app.Quit()
    End If

Open in new window

0
RIASAuthor Commented:
Thanks Roy...trying
0
RIASAuthor Commented:
Thanks Roy,
Its working only issue is when I open .csv file in notepad , it has
rows like
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

To add more information :
 If there are column names at the end of  the excel sheet , and there is no data in that column (cell) it comes with the blank with ','
Basically it should detect that there is no data after this column and stop adding the comma (,,,,)


How can I avoid this at the end of the column with used range.

Thanks
0
RIASAuthor Commented:
Also Roy , Any suggestions if  there is only one worksheet then the .csv should be saved as Workbookname  and if there are more worksheets then the file should be saved as workbookname_worksheetname.

Thanks
0
Roy CoxGroup Finance ManagerCommented:
It looks like those rows may be caused by some unseen characters in the worksheets.

Try this code to allow for the possibility of the workbook only having one worksheet.

Dim officeType As Type = Type.GetTypeFromProgID("Excel.Application")
Dim WS As Excel.Worksheet
If officeType Is Nothing Then
Else
    Dim app As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    app.DisplayAlerts = False
    Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("C:\test.xlsx")
    Dim newFileName As String = System.IO.Directory.GetCurrentDirectory() & "\DataMigration"

    With excelWorkbook
        If .Worksheets.Count > 1 Then
            For Each WS In .Worksheets
                If WS.Visible = xlSheetVisible Then
                    WS.Copy
                    WS.SaveAs Filename:=SaveToDirectory & newFileName & "-" & .WS.Name & ".csv", FileFormat:=xlCSV
                End If
            Next WS
        Else: WS.Copy
            WS.SaveAs Filename:=SaveToDirectory & newFileName & ".csv", FileFormat:=xlCSV
        End If
        .Close()
    End With
    app.Quit()
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Thanks Roy!! I will try by tomorrow and brb! Once again thanks a ton!
0
RIASAuthor Commented:
Roy,
Thanks. I am still getting
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

To add more information :
 If there are column names at the end of  the excel sheet , and there is no data in that column (cell) it comes with the blank with ','
Basically it should detect that there is no data after this column and stop adding the comma (,,,,)


How can I avoid this at the end of the column with used range.

Thanks
0
RIASAuthor Commented:
Thanks a ton!!!
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help.

To get  rid of those characters I think you would need to clean the original sheets by selecting the rows and columns outside the data and use rhe delete button
0
RIASAuthor Commented:
Thanks. Is there a way to clean the csv file programatically?
0
Roy CoxGroup Finance ManagerCommented:
I don't know of any.

You need to either clean the source excel file or the resulting csv.

Either case you could use Find & Replace on '
0
RIASAuthor Commented:
Ok. Thanks !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.