Activate last Excel Workbook after multiple runs

Question

I have a macro running in MS Project that exports timescaled data to a pre-formatted Excel Workbook. The export works fine. The issue is doing the final formatting in Excel. This is the code snippet that sets up the instance of Excel and creates a new Workbook.

'set up an new instance of Excel, or if Excel is not running, start it
On Error Resume Next
Set Xl = GetObject(, "Excel.application")
If Err <> 0 Then
    On Error GoTo 0
    Set Xl = CreateObject("Excel.Application")
    If Err <> 0 Then
        MsgBox "Excel application is not available on this workstation" _
            & vbCr & "Install Excel or check network connection", vbCritical, _
            "Project Data Export - Fatal Error"
        FilterApply Name:="all tasks"
        Set Xl = Nothing
        On Error GoTo 0     'clear error function
        Exit Sub
    End If
End If
On Error GoTo 0
'create a workbook with two worksheets
Xl.Workbooks.Add
Xl.ActiveWorkbook.Worksheets(1).Name = "Resource Information"
Set WS1 = Xl.ActiveWorkbook.Worksheets(1)
Xl.ActiveWorkbook.Worksheets(2).Name = "Monthly Data"
Set WS2 = Xl.ActiveWorkbook.Worksheets(2)

'Keep Excel in the background and minimized until spreadsheet is done (speeds transfer)
Xl.Visible = False
Xl.ScreenUpdating = True
Xl.DisplayAlerts = False

Then the macro proceeds to execute the export. When finished the intent is to do some final formatting and display the result in Excel. For the first run, all is great. The problem occurs when multiple runs are performed. When the Excel object is set back to visible, the first Workbook created shows as the active visible workbook. So I thought, okay, I'll just insure the last Workbook created (current run) is the active focus so it can be formatted. To do that I added this set of code. When the code is stepped through, it works fine but when the code runs "free" a runtime error occurs when it comes to selecting, WS1.Rows(2).Select, because the active Workbook is, you guessed it, Workbook1, not the latest Workbook.

'format completed worksheets
Xl.Visible = True
'make sure latest workbook is focus
Xl.Workbooks(Xl.Workbooks.Count).Activate
Set WS1 = Xl.Workbooks(Xl.Workbooks.Count).Worksheets(1)
Set WS2 = Xl.Workbooks(Xl.Workbooks.Count).Worksheets(2)
WS1.Columns("A:F").AutoFit
WS1.Rows(2).Select
Xl.ActiveWindow.FreezePanes = True
WS1.Range("A1").Select
WS2.Activate
WS2.UsedRange.Columns.AutoFit
WS2.Range("C3").Select
Xl.ActiveWindow.FreezePanes = True
TotTim = Timer - TimSt
'temporarily hide Excel to display result
Xl.Visible = False

MsgBox "Export is complete" & vbCr & _
    "   Export time: " & TotTim & " sec", vbInformation
Xl.Visible = True
Xl.Workbooks(Xl.Workbooks.Count).Activate
Set Xl = Nothing
End Sub

Any suggestions?

I expected the latest Workbook created to be the active Workbook so the selection based formatting can be accomplished

Answer 1

When creating the workbook, capture the new workbook in a variable for future reference:

'create a workbook with two worksheets
Dim wbk As Workbook
Set wbk = Xl.Workbooks.Add

Then later:

'make sure latest workbook is focus
wbk.Activate
Set WS1 = wbk.Worksheets(1)
Set WS2 = wbk.Worksheets(2)
WS1.Columns("A:F").AutoFit
Ws1.Activate
WS1.Rows(2).Select