Help: VBS to Convert XLS to CSV

[luciddreams]

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!

'* 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

 

[Tweak155]

Can you try the following?

'* 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 = oFSO.GetParentFolderName(sFileName) & "\{sheet}.csv"


    '* Do Work
    Set oExcelFile = oExcel.Workbooks.Open(sAbsoluteSource)
    Dim ws
    For Each oSheet in oExcelFile.Sheets
        sThisDestination = Replace(sAbsoluteDestination,"{sheet}",oSheet.Name)
        Set ws = oExcelFile.Sheets(oSheet.Name)
        ws.Range("A1:Z1").Value = "a"
        ws.Select
        If oFSO.FileExists(sThisDestination) Then
            oFSO.DeleteFile sThisDestination
        End If
        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
    Exit Function
End Function

 

[luciddreams]

Tweak155,

Thank you for the reply. I tried the code, however, it does not create a .csv.

If it helps, the .bat command to convert is

XlsToCsv.vbs Product-Feed.xls Product-Feed.csv

 

[Tweak155]

Tweak155,

Thank you for the reply. I tried the code, however, it does not create a .csv.

If it helps, the .bat command to convert is

XlsToCsv.vbs Product-Feed.xls Product-Feed.csv

I tried to hack it without actually testing it (I assumed your code already generated something and went from there)... I will reply again with tested code 🙂

 

[Tweak155]

Actually it seems to be working, try only passing in "Product-Feed.xls" instead of both parameters... let me know!

EDIT:

You need to add the path, I was doing that in my test .... "C:\PathToXLS\Product-Feed.xls"

 

[razel]

It's using the Excel.Application object. Make sure Office Excel is installed, probably is on your machine, but if you're running off a server then often not.

 

[luciddreams]

It works, thank you!! This really helps me out!

Adding the direct path is what was required (it wasn't required before, but it's all good).