Issue with batch script with vbscript command in the batch file

Question

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.

score 1 · Accepted Answer · answered Jun 25 '15 at 14:33

1

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.

answered Jun 25 '15 at 14:33

Alex K.

171,639
30
264
288

Thanks for the help!! It actually made sense to me and it's working well now. Is there a way to change the .csv file to the name of the parent directory? For example, if the path was subfolder1\file1.xlsx, if I wanted to rename the new .csv file to subfolder1, how would i do that? – needhelpwithR Jun 25 '15 at 14:58

score 0 · Answer 2 · answered Jun 25 '15 at 14:57

0

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

answered Jun 25 '15 at 14:57

Bond

16,071
6
30
53

Is strNewName = Left(objFile.Path, Len(objFile.Path) - 4) always reliable? Seems like subtracting 4 from each new name could pose some problems. Also how would i change each new .csv file to the same name as the parent folder name? – needhelpwithR Jun 25 '15 at 15:02
The line before checks to see if the filename ends in `.xlsx`, so it should be pretty reliable. How are you going to use the parent folder name? What if two XLSX files exist in the same subfolder? Wouldn't they end up using the same parent folder name for the CSV? – Bond Jun 25 '15 at 15:03
Oops, what I actually needed was consolidating all of the new .csv files in each folder into one file with the folder name. For example file1.csv, file2.csv are created, both are in subfolder1. A new .csv file that contains the contents of file1 and file2 would be created called subfolder1.csv. – needhelpwithR Jun 25 '15 at 15:16
There are a number of ways to consolidate CSV files. Since they're just text files, you can `copy` them to the same destination file and Windows will merge them. But that's out of the scope of your original question. If you can't figure out how to do it via Google/SO, go ahead and submit a new question specific to CSV consolidation. – Bond Jun 25 '15 at 15:27

Issue with batch script with vbscript command in the batch file

2 Answers2