Execute .vbs from a batch for a specific workbook

Question

I have a .bat file and I'm using it to execute a .vbs which I've pulled out of an excel workbook.

Currently, my .bat file looks like:

echo Starting program

start C:\Users\midi\Desktop\Refresh_Data_Connection.vbs

echo Finish program 

However, the issue with my .vbs file is that it doesn't reference a specific workbook which I need it to. How could I set the .vbs to alter a specific workbook stored on my desktop? Any help/feedback or push in the right direction would be really helpful, thanks in advance!

My vbs is:

Sub RefreshConns()


With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Sheets("Run Macro").Activate
Dim connName As String ' connection name
Dim connStr As String ' connection string
Dim sqltext ' SQL text

Dim TempconnName As String ' temporary connection name
Dim TempconnStr As String ' temporary connection string
Dim Tempsqltext ' temporary SQL text
Dim i As Integer
Dim SiteName As String

SiteName = ActiveSheet.Cells(1, 2)
'MsgBox (SiteName)
For i = 5 To 11
connName = ActiveSheet.Cells(i, 1).Value
connStr = ActiveSheet.Cells(i, 2).Value
sqltext = ActiveSheet.Cells(i, 4).Value
'MsgBox (connName)

TempconnStr = Replace(connStr, "SiteNameVBA", SiteName)
'Debug.Print (ActiveWorkbook.Connections(connName).ODBCConnection.Connection)
'MsgBox (TempconnStr)
'Tempsqltext = Replace(sqltext, "SiteNameVBA", SiteName)

'On Error Resume Next
ActiveWorkbook.Connections(connName).ODBCConnection.CommandText = sqltext
ActiveWorkbook.Connections(connName).ODBCConnection.Connection = "ODBC;" & TempconnStr
ActiveWorkbook.Connections(connName).Refresh

Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub


Public Function ZeroToBlank(x As String) As String
If x = "0" Then
    ZeroToBlank = ""
Else
    ZeroToBlank = x
End If
End Function

Answer 1

Set xlBook = GetObject("C:\Users\User\Documents\Super.xls")
For each wsheet in xlbook.worksheets
    msgbox wsheet.name
next

Is how. Remember there are no excel constants in VBS, there is no dim x as something as vbs only does variants. so just dim x.

Other ways to a running copy of excel

Set GetExcelApp = GetObject("", "Excel.Application")
Msgbox GetExcelApp

To start a new excel and showing an excel constant replaced by it's value (43).

set xlapp = createobject("Excel.Application")
xlapp.Workbooks.Open "C:\Users\User\Documents\Super.xls"
'43 is 95/97 look up xlExcel9795 in object browser to see other options
xlapp.ActiveWorkbook.SaveAs "C:\Users\User\Documents\Super.xls", 43

Answer 2

One way is to declare a system environment variable using setx. Running your batch with administration privilege try this:

setx PATHFILE "/path/file.xls" /M

Check if is declared:

set | find "PATHFILE"

And read it with the function Environ() to open you Workbook in your vbs:

Workbooks.Open Environ("PATHFILE") 

EDIT

You do not need to use system environment variable (/m argument) as you can use only user environment. You need to just remember that the programs need to restart to read the user and system environment variable.