Convert excel file to csv format

Hello VB gurus,

I have unique problem with saving excel file in CSV format. I want to do in batch process.

Steps : Read excel file as input and create another csv format file.

Please help me wth my problem

Thanks in Advance
Raj

reference the MS Excel Object library

simple example to loop through a directory and convert...

VB Code:

Dim xls As Excel.Application
Private Sub Form_Load()
Set xls = New Excel.Application
xls.Visible = True 'comment this out if you dont want excel to show
tmp = Dir("C:\path\*.xls")
Do While tmp > ""
    xls.Workbooks.Open "C:\path\" & tmp
    xls.ActiveWorkbook.SaveAs FileName:=Replace("C:\path\" & tmp, ".xls", ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False
    xls.Workbooks.Close
    tmp = Dir
Loop
xls.Quit
End Sub

I found this thread. I'm trying to batch convert several XLS files into CSV files. This works but the problem is:

I get a message box that asks me if I want to save the changes (the CSV file was already created and so its asking me to save over it).

Question:

How can I get this to batch process without the message box popping up?

I also found this thread, post #5 has some info but couldn't figure this out.

http://www.vbforums.com/showthread.php?t=391665

Thanks!!!

1. I referenced the MS Excel Object library.

2. Have multiple XLS files in a directory called c:\cmo\

3. Modified the code a bit to make it work:

VB Code:

Private Sub cmdXlsCsv_Click()
    Dim xls As Excel.Application
    Dim tmp As String
    Set xls = New Excel.Application
    'xls.Visible = True 'comment this out if you dont want excel to show
    tmp = Dir("C:\cmo\*.xls")
    Do While tmp > ""
    xls.Workbooks.Open "C:\cmo\" & tmp
    xls.ActiveWorkbook.SaveAs FileName:=Replace("C:\cmo\" & tmp, ".xls", ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False
    xls.Workbooks.Close
    tmp = Dir
    Loop
    xls.Quit
End Sub

You need to add the argument to not save changes since your using .SaveAs

VB Code:

xls.Workbooks.Close SaveChanges:=False

I'm getting a VB message, "Compile error: Wrong number of arguements or invalid property assignment" xls.Workbooks.Close

I tried this:

VB Code:

xls.Workbooks.Application.ActiveWorkbook.Close SaveChanges:=False

It seems to work but seems like there could be a shorter way to write that, looks funky (does work though)

original code with error:

http://www.oberman.info/forums/message.jpg

VB Code:

Private Sub cmdXlsCsv_Click()
    Dim xls As Excel.Application
    Dim oWB As Excel.Workbook
    Dim tmp As String
    Set xls = New Excel.Application
    'xls.Visible = True 'comment this out if you dont want excel to show
    tmp = Dir("C:\cmo\*.xls")
    Do While tmp > ""
        Set oWB = xls.Workbooks.Open("C:\cmo\" & tmp)
        oWB.SaveAs FileName:=Replace("C:\cmo\" & tmp, ".xls", ".csv", , , vbTextCompare), FileFormat:=xlCSVMSDOS, CreateBackup:=False)
        oWB.Close SaveChanges:=False
        tmp = Dir
        Set oWB = Nothing
    Loop
    xls.Quit
    Set xls = Nothing
End Sub

This should do it.

It works great, Thanks !!! ... had to get rid of the ) after CreateBackup:=False) ...

I added an hour glass also since it is a batch routine.

VB Code:

Private Sub cmdXlsCsv_Click()
 
    Screen.MousePointer = vbHourglass
    
    Dim xls As Excel.Application
    Dim oWB As Excel.Workbook
    Dim tmp As String
    Set xls = New Excel.Application
    tmp = Dir("C:\cmo\*.xls")
    Do While tmp > ""
        Set oWB = xls.Workbooks.Open("C:\cmo\" & tmp)
        oWB.SaveAs FileName:=Replace _
        ("C:\cmo\" & tmp, ".xls", ".csv", , , vbTextCompare), _
        FileFormat:=xlCSVMSDOS, CreateBackup:=False
        oWB.Close SaveChanges:=False
        tmp = Dir
        Set oWB = Nothing
    Loop
    xls.Quit
    Set xls = Nothing
    
    Screen.MousePointer = vbDefault
End Sub

:) You might also want to add a DoEvents in the loop to free the CPU to do other things so it doesnt seem like its frozen during large batches.

If the same file is converted from XLS to CSV twice, then the message box come up file already exists. (this would happen because program is run twice, which shouldn't happen ... but ...) So even though it should never happen, I figure I need some error handling in this.

If the user hits the save button all is find, cancel errors the vb program out.

I tried this error code but the program acts like it is trying to do something, but it just sits there.

VB Code:

Private Sub cmdXlsCsv_Click()
 
    On Error GoTo MyError
    
    cmdXlsCsv.Enabled = False
 
    Screen.MousePointer = vbHourglass
    
    Dim xls As Excel.Application
    Dim oWB As Excel.Workbook
    Dim tmp As String
    Set xls = New Excel.Application
    tmp = Dir("C:\cmo\*.xls")
    Do While tmp > ""
        Set oWB = xls.Workbooks.Open("C:\cmo\" & tmp)
        oWB.SaveAs FileName:=Replace _
        ("C:\cmo\" & tmp, ".xls", ".csv", , , vbTextCompare), _
        FileFormat:=xlCSVMSDOS, CreateBackup:=False
        oWB.Close SaveChanges:=False
        tmp = Dir
        Set oWB = Nothing
    Loop
    xls.Quit
    Set xls = Nothing
    
    Screen.MousePointer = vbDefault
 
    cmdMove.Enabled = True
    
MyError:
 
   If Err.Number = 70 Then
   MsgBox "Csv File Exixts", vbOKOnly + vbExclamation
   End If
 
End Sub

This seems to get it. Needed to turn off Excel and the VB Glass

VB Code:

MyError:
 
   If Err.Number = 1004 Then
   MsgBox "Csv File Exixts", vbOKOnly + vbExclamation
   xls.Quit
   Set xls = Nothing
   Screen.MousePointer = vbDefault
   End If

problem with that, you have not put resume next in your error handler, so no other files will be processed after the error

my choice would be to test if the file exists before save

VB Code:

if dir(filename) = "" then 'save as code, where filename is the csv filename

One other thing I'd like to do if possible is when the spreadsheet file is opened, before it is saved as a CSV, is to remove the formatting ... that would help immensely.

I've looked all over the place to try to figure it out but can't seem to get it.

Thanks.

I figured it out:

Columns("A:Z").Select
Selection.NumberFormat = "General"

Hi guys

I also have a question on the VB codes above.

The codes can work well,but they can only convert one sheet to csv file,

for example, if the excel spreadsheet has 2 or more sheet(sheet1 sheet2 sheet3...) it can only convet sheet1 or sheet2.. to csv file..

I am a new comer to VB, could you help me?

Thank you