How can I use absolute path in batch file or force vbs not to need the path?

Question

I am writing a batch file and part of it converts .csv to .xlsx. I've been able to incorporate the solution from this post:

Convert .CSV to .XLSX using command line

Dim file, WB

With CreateObject("Excel.Application")
On Error Resume Next
For Each file In WScript.Arguments
    Set WB = .Workbooks.Open(file)
    WB.SaveAs Replace(WB.FullName, ".csv", ".xlsx"), 51
    WB.Close False
Next    
.Quit
End With

It works great, but the only issue is that I must pass an absolute path into the batch file to get it to work, like so:

CSV2XLSX.vbs C:\Users\Data\ktq\abc.csv

As explained in the original thread, it doesn't work just by doing the following:

CSV2XLSX.vbs abc.csv

This is a bit of a pain as if I move the folder I need to update the path. Is there any way I can force vbs to just take in the above command by correctly finding the file, or can I get the absolute path from the file in question and pass it in somehow? Any help would be great!

score 1 · Accepted Answer · answered Nov 09 '16 at 15:14

The following script will provide you with the path of your running .vbs script. You can use such script to determine the path of your files.
So if, for instance, your script is stored in C:\Users\Data\ktq\yourscript.vbs", the script bellow will outputstrMainPathasC:\Users\Data\ktq` (without the trailing backslash):

Dim objShell : Set objShell = CreateObject("Wscript.Shell")
Dim strPath : strPath = Wscript.ScriptFullName
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.GetFile(strPath)
Dim strMainPath : strMainPath = objFSO.GetParentFolderName(objFile) 

'Cleaning no longer needed objects
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing

Print strMainPath

So, supposing your script is in the same folder as your .csv files, you will be able to find them all. If you have another level of files, say in C:\Users\Data\ktq\myFiles\ you will also be good to go if you just change the Print part of the code above with this:

Print strMainPath & "\myFiles\"

Adapting it to your code, it should be something like this:

Dim objShell : Set objShell = CreateObject("Wscript.Shell")
Dim strPath : strPath = Wscript.ScriptFullName
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.GetFile(strPath)
Dim strMainPath : strMainPath = objFSO.GetParentFolderName(objFile) 

'Cleaning no longer needed objects
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing

Dim file, WB

With CreateObject("Excel.Application")
    On Error Resume Next
    For Each file In WScript.Arguments
        Set WB = .Workbooks.Open(strMainPath & "\" & file)
        WB.SaveAs Replace(WB.FullName, ".csv", ".xlsx"), 51
        WB.Close False
    Next    
    .Quit
End With

How can I use absolute path in batch file or force vbs not to need the path?

1 Answers1