I need to programmaticaly via a VBA/VBS script export all worksheets (4 in total and I know the names) to worksheet named csv files in the same folder, without loading excel and running the macro manually.
Ideally the script would take in the source filepath/filename.xls and the export filepath as command line arguments.
I have read many Excel VBA scripts for doing just that within Excel and I have seen some for loading an excel workbook to export the first sheet. However when I try to blend the two I get this error:
(1,12) Expected End of Statement
Dim source As Workbook
Set source = Application.Workbooks.Open(WScript.Arguments.Item(0), ReadOnly:=True)
For Each sheet In source.Sheets
.SaveAs Filename:= WScript.Arguments.Item(1) & Source.Sheets.Name, FileFormat:=xlCSV
Next sheet
wb.Close
A vbs to run this code would look something like this.
two key points to note compared to your VBA above
you can't used a named constant such as xlCSV
in vbscript, hence the use of 6 below as the CSV format
Dim strFilename
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")
strFilename = "C:\temp\test.xlsx"
If objFSO.fileexists(strFilename) Then
Call Writefile(strFilename)
Else
wscript.echo "no such file!"
End If
Set objFSO = Nothing
Sub Writefile(ByVal strFilename)
Dim objExcel
Dim objWB
Dim objws
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strFilename)
For Each objws In objWB.Sheets
objws.Copy
objExcel.ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", 6
objExcel.ActiveWorkbook.Close False
Next
objWB.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With