Re-Initializing "ThisWorkbook.Path"

Question

First, thanks to those of you who gave me the suggestion on using "ThisWorkbook.Path". It worked like a charm.

However, my code walks through seven (7) workbooks and when using "ThisWorkbook.Path" I can not re-initialize the "This.Workbook". Let me elaborate.

This is the workbook where the Macro resides:

Workbooks("Financial_Aggregator_v3.xls").Activate

This is the first workbook where the code adds a tab and does sub-totals. Basically, ThisWorkbook.Path works here:

Workbooks("Chapter_7-10_Mechanical.xls").Activate

After doing what I need done with "Mechanical" I have the following code snippet, which never turns out TRUE:

Workbooks("Financial_Aggregator_v3.xls").Activate
If FileThere(ThisWorkbook.Path & Application.PathSeparator & "Chapter_7-90_ECS_1_LLC.xls") Then

The code for the function, which works for the "Mechanical" sheet is:

Function FileThere(FileName As String) As Boolean
    FileThere = (Dir(FileName) > "")
End Function

FYI, I tried to break all of the different Workbooks into different Sub(), but that didn't work. I also triple-checked the name of the workbooks.

Thanks in advance.

Answer 1

"ThisWorkbook" in Excel.VBA is sort of like "Me", in that it only applies to the workbook (.XLS) that actually holds the VBA code that is executing the "ThisWorkbook". What you need to do is to use Workbook objects to abstract the particular workbook that you want to test or manipulate.

Try something like this:

Public Sub TestWB()
    Dim CurrWB As Workbook

    'To get a workbook into our object variable:'
    Set CurrWB = Workbooks("Chapter_7-10_Mechanical.xls")

    'To Change the .Path:'
    CurrWB.SaveAs NewFileName, AddToMru:=True

End Sub