Opening Excel file from Access VBA not working

[Lynch225]

Hey all,

I am having issues when trying to open an Excel file from Access VBA. Here's the code I have so far:

Public Sub Prod()

Dim xlApp As Object
Dim xlWorkbook As Object
Dim strProd As String

'Prepare Staff list
Set dbs = CurrentDb()
DoCmd.SetWarnings False
strProd = "C:\Docs\Hours.xlsm"
Set objrst = dbs.OpenRecordset("Report Date")

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Visible = True
    .DisplayAlerts = True
    .CutCopyMode = False
    Set xlWorkbook = Workbooks.Open(strProd)
        xlWorkbook.Activate
        Set xlSheet = xlWorkbook.Worksheets("VTO")
        xlSheet.Activate
        xlSheet.Range("D2").CopyFromRecordset objrst
    .Run "Combine"
    xlWorkbook.Save
    xlWorkbook.Close
    .DisplayAlerts = True
    .Quit
End With
    
End Sub

When I step into the code and I get to the lines that say:

Set xlWorkbook = Workbooks.Open(strProd)
        xlWorkbook.Activate

The code will open the Excel application, but the workbook is not physically opening for some reason, so thus it won't run the "Combine" macro. Then when I get the error, it's telling me that the workbook is open but is not visible. Hopefully this makes sense. I'm sure it's something small, but my brain hurts trying to figure it out.

Thanks!

[ADezii]

It appears as though you need the Qualifier "." between the Application (xlApp) and the Collection (Workbooks), namely:

 Set xlWorkbook = .Workbooks.Open(strProd)

[Lynch225]

Wow, something as simple as a period. Worked perfectly, thanks!