CODEWITHSUNDEEP
csvbatch-filevbscript
gshockxcc
gshockxcc

Reputation: 19

How to run .vbs through .bat file to convert .xls to .csv

I am completely new to scripting, so please forgive my ignorance.
(Running Windows 10) I found a working solution to convert .xls files to .csv using the .vbs from this post:

Convert xls to csv.

The files I'm working with have multiple sheets, and the vbs works by dragging the file(s) onto the vbs file icon to execute. I don't understand how the vbs gets the input arguments. I copied this code posted by Chris Rudd

    'Courtesy of Chris Rudd on stackoverflow.com
'Modified by Christian Lemer
'plang
'ScottF
' https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

'* Usage: Drop .xl* files on me to export each sheet as CSV

'* Global Settings and Variables
'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")

    sPrompt = vbCRLF & _
        "A filename was received that doesn't appear to be an Excel Document." & vbCRLF & _
        "Do you want to skip this and all other unrecognized files?  (Will only prompt this once)" & vbCRLF & _
        "" & vbCRLF & _
        "Yes    - Will skip all further files that don't have a .xl* extension" & vbCRLF & _
        "No     - Will pass the file to excel regardless of extension" & vbCRLF & _
        "Cancel - Abort any further conversions and exit this script" & vbCRLF & _
        "" & vbCRLF & _
        "The unrecognized file was:" & vbCRLF & _
        sFilename & vbCRLF & _
        "" & vbCRLF & _
        "The path returned by the system was:" & vbCRLF & _
        oFSO.GetAbsolutePathName(sFilename) & vbCRLF

    sTitle = "Unrecognized File Type Encountered"

    sResponse =  MsgBox (sPrompt,vbYesNoCancel,sTitle)
    Select Case sResponse
    Case vbYes
        gSkip = True
    Case vbNo
        gSkip = False
    Case vbCancel
        oExcel.Quit
        WScript.Quit(10)    '*  10 Is the error code I use to indicate there was a user abort (1 because wasn't successful, + 0 because the user chose to exit)
    End Select

    PromptForSkip = gSkip
    Exit Function
End Function

This works well for my needs, but I want to run it hourly and save the .csv files to a new directory.

I tried to run the .vbs using the Task Scheduler, but it only opens the file in my text editor, it doesn't execute. My thought was to create a .batch file that runs the .vbs. I thought I could call the .vbs with PowerShell commands like this: 

Start "XlsToCsv"
Start "XlsToCsv.vbs"

But both of those have the same effect of opening the .vbs in the text editor.

Perhaps a simpler question is, "How do I run a .vbs file from the PowerShell or the Command Prompt?"

Any help would be greatly appreciated.

Upvotes: 1

Views: 1338

Answers (3)

Io-oI
Io-oI

Reputation: 2565

  • Take look in this file.gif:

enter image description here

I'm understood that this only works if you're using the file by Drag and Drop passing as arguments...

You can see instructions left by author about how to use in the in 7rd line:

Drop  .xl* files on me to export each sheet as CSV

Obs.: .xl* is equal to "any file with (.xl)"+any" == *.xlsx, *.xlsm...

  • Take look in this file.gif that you can see how to pass arguments in , (is the same to & ) files by Drag and Drop:

enter image description here

This file.png show how to use arguments with the

![enter image description here

The code you have tried are not using/passing any arguments (parameters), without pass any arguments, (one or more files) this will always fail!

  • If you need try using some like: 

"XlsToCsv.vbs" "one_file_dot.extensio_equal_xlsx_.xlsx" "one_more_another_file_dot.extensio_equal_xlsx_too.xlsx"

rem :: observing if you are try in a different folder where the vbs + xlsx file are, try this: 

"d:\folder\where\vbs\are\XlsToCsv.vbs" "d:\folder\where\xlsx\file1\are\1file_dot.extension_equal_xlsx_.xlsx" "d:\folder\where\xlsx\file1\are\one\more\are\too\one_more_another_file_dot.extensio_equal_xlsx_too.xlsx"

__

This is who I have using your files in same folder where the files **.xlsx* are, so, no need passing the drive\folder path: 

wscript 58924333.vbs "012019.xlsx" "022019.xlsx"
rem ::  or   ::
cscript 58924333.vbs "012019.xlsx" "022019.xlsx"

So sorry my limited English

Upvotes: 0

T3RR0R
T3RR0R

Reputation: 2951

This method works consistently in the batch environment, however cannot use doublequotes as would be advisable for filepaths with spaces.

Start filepath\name.vbs

no Doublequotes.

This method works Consistently in cmd.exe console, however fails in .bat programs:

WSscript.exe "filepath\name.vbs"

Upvotes: 1

Raghav venkat
Raghav venkat

Reputation: 166

Wscript/Window Script host provides an environment to execute scripts in a variety of languages and Cscript starts a script in command line environment.

So, If you want to run your script in console use cscript.exe and wscript.exe if you don't want the console window. So, as T3RR0R mentioned you need to use WScript command to run it.

Sometimes, executing a VBscript in windows opens text editor rather than running it. This is due to changes in the default file associations. Some time antivirus will also do this in order to protect your system. In that case, you need to change the registry check this link https://superuser.com/questions/1108349/change-default-program-for-opening-vbs-files

Upvotes: 0

Related Questions