CODEWITHSUNDEEP
windowsbatch-filecmdvbscript
Sergiu Z
Sergiu Z

Reputation: 163

How to I include a vbscript step in a batch file?

What I'm trying to do: I have 3 xls files, i get by email, named as such (date stamp changes every time): 

CDC aaa 01 01 2018.xls
CDC bbb 01 01 2018.xls
CDC ccc 01 01 2018.xls

I want to:

Here's my script, so far, that fails:

@ECHO OFF
setlocal enableextensions enabledelayedexpansion


for %%F in ("*.xls") do ( 
    REM get filename only
    set tmp=%%~F

    REM look for aaa
    echo.!tmp! | findstr /i "aaa" 1>nul
    if errorlevel 1 (
        echo. Not aaa.
    ) ELSE (
        echo. Found aaa
        set fileout=aaa.csv
    )

    REM look for bbb
    echo.!tmp! | findstr /i "bbb" 1>nul
    if errorlevel 1 (
        echo. Not bbb.
    ) ELSE (
        echo. Found bbb.
        set fileout=bbb.csv
    )

    REM look for ccc
    echo.!tmp! | findstr /i "ccc" 1>nul
    if errorlevel 1 (
        echo. Not ccc.
    ) ELSE (
        echo. Found ccc.
        set fileout=ccc.csv
    )
    REM convert and rename file
    xlstocsv.vbs "!tmp!" "!fileout!"
)

When I run this I get a Windows Script Host error message saying:

Script: C:\[bla]\xlstocsv.vbs
Line: 17
Char: 1
Error: The file could not be accessed. Try one of the following:
- Make sure folder exists
- Make sure the folder that contains the file is not read-only
- Make sure the file name does not contain anu of the following characters: < > ? [ ] : CDC aaa 01 01 2018.xls or *
- Make sure the file/path name doesn't contain more than 218 characters
- Code: 800A03EC
- Source: Microsoft Excel

I can confirm the files/folder is not read only, I can confirm there are no special characters in the path/filename, nor is the path longer than 218 characters.

I can also confirm that the excel files are not corrupt or bad in any way.

I can also say that while I see the error message, if I try to open ANY of the 3 files in excel (not just the one I get the error for), excel throws the exact same error as above.

If I run this script, it works just fine:

setlocal enableextensions enabledelayedexpansion

for %%F in ("*.xls") do ( 
    xlstocsv.vbs "%%~nF.xls" "%%~nF.csv"
)

but this version of the script does not rename my files to the desired filenames, just changes the extension.

Here's the VBScript:

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Upvotes: 1

Views: 171

Answers (1)

Compo
Compo

Reputation: 38623

Given the provided file naming structure, the following script should perform the task required:

@Echo Off
Set "Ext1=.xls"
Set "Ext2=.csv"
Set "ScrV=C:\bla\xlsToCSV.vbs"
Set "SrcD=C:\bla\bla"
Set "Str1=aaa"
Set "Str2=bbb"
Set "Str3=ccc"

CD /D "%SrcD%" 2>Nul || Exit /B
For %%A In ("?* %Str1% ?*%Ext1%","?* %Str2% ?*%Ext1%","?* %Str3% ?*%Ext1%") Do (
    For /F "Tokens=2" %%B In ("%%~nA") Do (Echo Found %%B
        CScript //B //NoLogo "%ScrV%" "%%A" "%%B%Ext2%"))

You should change only the lines 4-8, by inserting the appropriate paths and strings. (Please do not add or remove any doublequotes, ", and do not include a trailing backslash, \ on your source directory path on line 5.)

This is only a batch script solution, VBScript Excel or filesystem issues would require a new question.

Upvotes: 1

Related Questions