CODEWITHSUNDEEP
windowsexcelcsvbatch-filevbscript
needhelpwithR
needhelpwithR

Reputation: 283

Issue with batch script with vbscript command in the batch file

I am new to batch scripting and vbscript. What I want to do is convert .xlsx Excel files into .csv Excel files, in multiple directories (Recursively). For example:

Main directory
   subdirectory1
      file1.xlsx
      file2.xlsx
   subdirectory2
      file3.xlsx
      file4.xlsx

I have made this batch script:

FOR /r %%a in (*.xlsx) do (
    SET filename=%%a
    ExceltoCSV.vbs %filename% *.csv
)

Inside the for loop is the ExceltoCSV.vbs. I got this code from this thread Convert XLS to CSV on command line, and I have tried the top 2 answers already (Both don't require downloading anything).

    if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

The error is saying that the ExceltoCSV.vbs file cannot be accessed. However, I believe the batch script is working, for example it would say:

SET filename=C:\folder\subfolder\test1.xlsx

then it calls:

ExceltoCSV.vbs C:\folder\subfolder\test1.xlsx *.csv

I am not sure what the problem is and I am currently very confused.

Upvotes: 2

Views: 639

Answers (2)

Bond
Bond

Reputation: 16311

You're using VBScript to convert. Why not just use it to iterate your folders as well? Then you don't have to worry about calling a VBS from a BAT and passing the proper args.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")
DoFolder "c:\mainfolder"
oExcel.Quit

Sub DoFolder(strFolder)

    ' Recursively process each subfolder...
    For Each objSubFolder In objFSO.GetFolder(strFolder).SubFolders
        DoFolder objSubFolder.Path
    Next

    ' Convert any XLSX files...
    For Each objFile In objFSO.GetFolder(strFolder).Files
        If StrComp(objFSO.GetExtensionName(objFile.Name), "xlsx", vbTextCompare) = 0 Then

            strNewName = Left(objFile.Path, Len(objFile.Path) - 4) & "csv"

            ' Convert...
            Set oBook = oExcel.Workbooks.Open(objFile.Path)
            oBook.SaveAs strNewName, 6
            oBook.Close False

        End If            
    Next

End Sub

Upvotes: 0

Alex K.
Alex K.

Reputation: 175776

The VBS needs to be in the same directory as the BAT file.

The issue is that variable expansion rules in a FOR loop mean that filename wont be set to the current file variables value; just use %%a instead:

FOR /r %%a in (*.xlsx) do (
   ExceltoCSV.vbs "%%a" "%%~dpna.csv"
)

You are passing the string "*.CSV" to the script which wont work, %%~dpna.csv takes the file name in %%a and changes the extension to .CSV.

The quotes are there to allow for spaces in paths.

Upvotes: 1

Related Questions