My VBS script is looking for file in wrong directory

Question

The macro I had written was working fine until I happened to change the file path to a relative one.

Here is the actual macro:

Public Sub refreshXLS()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Path = ThisWorkbook.Path & "\requiredSource\TestData1.xlsm"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(Path)

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    For Each file In folder.Files
        If Right(file.Name, 4) = "xlsx" Or Right(file.Name, 3) = "xls" Then
            Workbooks.Open Path & file.Name
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            ActiveWorkbook.Close True
         End If
    Next

        With Application
        .DisplayAlerts = False
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = False
    End With
End Sub

I updated it to:

Public Sub refreshXLS()

    Dim xlApp
    Dim xlBook
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim fullpath
    fullpath = fso.GetAbsolutePathName(".")
    Set fso = Nothing
    Set xlApp = CreateObject("Excel.Application")
    Path = xlApp.Workbooks.Open(fullpath & "\TestData1.xlsm")
    Set folder = fso.GetFolder(Path)

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    For Each file In folder.Files
        If Right(file.Name, 4) = "xlsx" Or Right(file.Name, 3) = "xls" Then
            Workbooks.Open Path & file.Name
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            ActiveWorkbook.Close True
         End If
    Next

        With Application
        .DisplayAlerts = False
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = False
    End With
End Sub

I also tried to use:

Workbooks.Open FileName:= ThisWorkbook.Path & "\TestData1.xlms".

This didn't help either.

Answer 1

Public Sub refreshXLS()
    Dim fso As Object
    Dim file As Object
    Dim extension As String

    Set fso = CreateObject("Scripting.FileSystemObject")

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    For Each file In fso.GetFolder(ThisWorkbook.Path).Files
        extension = LCase(fso.GetExtensionName(file.Path))
        If extension = "xlsx" Or extension = "xls" Then
            With Workbooks.Open(file.Path)
                .UpdateLink Name:=.LinkSources
                .Close True
            End With
        End If
    Next

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = False
    End With
End Sub

This will process all the files in the same folder where the current xlsm file is stored. If the files to be processed are under the requiredSource folder then you should change the GetFolder to something like

For Each file In fso.GetFolder(fso.BuildPath(ThisWorkbook.Path, "RequiredSource")).Files

edited to adapt to comments

The vbs file could be something like (assumes the previous refreshXLS is inside a module)

Option Explicit 

Const macroWorkbook = "TestData1.xlsm"
Const macroName = "refreshXLS"

Dim fso
    Set fso = WScript.CreateObject("Scripting.FileSystemObject")

Dim workbook    
    With CreateObject("Excel.Application") 
        Set workbook = .Workbooks.Open(fso.BuildPath( _ 
            fso.GetFile( WScript.ScriptFullName ).ParentFolder.Path _ 
            , macroWorkbook _ 
        ))
        .Application.Run "'" & Replace(workbook.Name, "'", "''") & "'!" & macroName
        .ActiveWorkbook.Close
        .Quit
    End With 

    WScript.Quit

Answer 2

Your code fails to work because you changed the variable Path.

You put Path = xlApp.Workbooks.Open(fullpath & "\TestData1.xlsm") which means the Path is now a Workbook Object, not a String anymore.

I suggest you to start using Option Explicit as a habit. In VBE, Options --> tick Require Variable Declaration.