Debug my code- variable filenames

[jmthompson]

Good morning,
I am building a macro intended to reconcile data listed in 2 different workbooks. I know the file and sheet names in my master workbook, but the second file is semi-unknown. I know the location of the file and the file always begins the same way, but the rest of the file name is a date & time range stamp.

I found code that Norie posted to find and open the file I need, but I have not been able to figure out how to then refer to that file in a Vlookup formula.


I am currently getting error 438 Object doesn't support this property or method with this line of code highlighted

 Cells(lastRowWFO + 4, "E").Formula = "=VLookup( ""Total"",'" & MyEOMPath & "[" & SrceBook & "]Sheet1'!$A:$B,2, False)"

Here is my code in its entirety:

Sub Reconciliation()
'sets date standard
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
'sets EOM file path
MyEOMPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\End of month files\"
'sets master WFO file path and name
MyMasterPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
'creates references to WFO master and EOM files- opens EOM file
Dim CurBook As Worksheet

Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
Dim lastRowWFO As Integer
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row
Dim wb As Workbook
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyEOMPath
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "sjersey*"
        .Execute
        
         Set wb = Workbooks.Open(.FoundFiles(1))
    End With
    
Dim SrceBook As Workbook
Set SrceBook = ActiveWorkbook
ActiveSheet.Name = "Sheet1"
CurBook.Activate
Range("E:E").Insert Shift:=xlToRight
Range("G:G").Insert Shift:=xlToRight

Cells(lastRowWFO + 4, "E").Formula = "=VLookup( ""Total"",'" & MyEOMPath & "[" & SrceBook & "]Sheet1'!$A:$B,2, False)"
End Sub

Any ideas?

 

[Jonmo1]

Try

Cells(lastRowWFO + 4, "E").Formula = "=VLookup( ""Total"",'" & MyEOMPath & "[" & SrceBook.Name & "]Sheet1'!$A:$B,2, False)"

HTH

 

[jmthompson]

jonmo1- I could kiss you!

Tired of helping me yet?

 

[Jonmo1]

jonmo1- I could kiss you!

Tired of helping me yet?

Not yet, but the Kissing part sure doesn't help your cause....LOL