- Jun 1, 2004
- 129
- 1
- 81
Hi guys,
I'll admit that I don't a lot about code... well, BASIC (not VBS), HTML, CSS... I can kind of get what other code is doing and sometimes I'm able to manipulate it successfully, sometimes not.
I'm using inventory software that only accepts .csv and my source is .xls.
I've setup a script to auto-download the .xls, and now I just need to automate the conversion so that my inventory program can auto-sync with its data. The script (I found) below can do that job, but it is in need of a few tweaks to be really usable to me.
What the VBS Script does:
It converts XLS to CSV via the Command Line (or drag and drop, though I don't use it that way)
What I need it to also do:
(1) auto-fill Row 1 with text--any text-from A1 to Z1. This is actually the most important out of these three requests.
(2) if the previous .csv already exists, write over it without prompt.
(3) It would be great if the script would rename the .csv the same name as the .xls proper. Right now, it renames Product-Feed.xls to Product-Feed.Product-Feed.csv. I read that the following code would fix this, but I'm not sure where to put it:
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"
Thank you in advance for any help!
I'll admit that I don't a lot about code... well, BASIC (not VBS), HTML, CSS... I can kind of get what other code is doing and sometimes I'm able to manipulate it successfully, sometimes not.
I'm using inventory software that only accepts .csv and my source is .xls.
I've setup a script to auto-download the .xls, and now I just need to automate the conversion so that my inventory program can auto-sync with its data. The script (I found) below can do that job, but it is in need of a few tweaks to be really usable to me.
What the VBS Script does:
It converts XLS to CSV via the Command Line (or drag and drop, though I don't use it that way)
What I need it to also do:
(1) auto-fill Row 1 with text--any text-from A1 to Z1. This is actually the most important out of these three requests.
(2) if the previous .csv already exists, write over it without prompt.
(3) It would be great if the script would rename the .csv the same name as the .xls proper. Right now, it renames Product-Feed.xls to Product-Feed.Product-Feed.csv. I read that the following code would fix this, but I'm not sure where to put it:
FOR /f "delims=" %%i IN ('DIR *.xlsx /b') DO ExcelToCSV.vbs "%%i" "%%~ni.csv"
Thank you in advance for any help!
Code:
'* Usage: Drop .xl* files on me to export each sheet as CSV
'* Global Settings and Variables
On Error Resume Next
Dim gSkip
Set args = Wscript.Arguments
For Each sFilename In args
iErr = ExportExcelFileToCSV(sFilename)
' 0 for normal success
' 404 for file not found
' 10 for file skipped (or user abort if script returns 10)
Next
WScript.Quit(0)
Function ExportExcelFileToCSV(sFilename)
'* Settings
Dim oExcel, oFSO, oExcelFile
Set oExcel = CreateObject("Excel.Application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
iCSV_Format = 6
'* Set Up
sExtension = oFSO.GetExtensionName(sFilename)
if sExtension = "" then
ExportExcelFileToCSV = 404
Exit Function
end if
sTest = Mid(sExtension,1,2) '* first 2 letters of the extension, vb's missing a Like operator
if not (sTest = "xl") then
if (PromptForSkip(sFilename,oExcel)) then
ExportExcelFileToCSV = 10
Exit Function
end if
End If
sAbsoluteSource = oFSO.GetAbsolutePathName(sFilename)
sAbsoluteDestination = Replace(sAbsoluteSource,sExtension,"{sheet}.csv")
'* Do Work
Set oExcelFile = oExcel.Workbooks.Open(sAbsoluteSource)
For Each oSheet in oExcelFile.Sheets
sThisDestination = Replace(sAbsoluteDestination,"{sheet}",oSheet.Name)
oExcelFile.Sheets(oSheet.Name).Select
oExcelFile.SaveAs sThisDestination, iCSV_Format
Next
'* Take Down
oExcelFile.Close False
oExcel.Quit
ExportExcelFileToCSV = 0
Exit Function
End Function
Function PromptForSkip(sFilename,oExcel)
if not (VarType(gSkip) = vbEmpty) then
PromptForSkip = gSkip
Exit Function
end if
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Exit Function
End Function