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
Printable View
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
This should do it.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
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