Open an Excel doc from within Access Refresh problem

[ECEK]

I have a database that runs an append query to a table then opens an Excel file that is linked to my table. like so.

Function OpenExcelFromAccess()
    Dim MyXL As Object
    
    Set MyXL = CreateObject("Excel.Application")
    With MyXL
        .Application.Visible = True
        .Workbooks.Open "C:\Folder One\Folder Two\Folder Three\My_Excel_File.xlsx"
    End With
End Function

I have ticked the box that requests that the Excel file refreshes on open but this does not happen ! I have to remember to manually press the refresh button on the ribbon.

I'm guessing that thisis due to the database being open?

Could I force my Excelfile to fresh the data on open?

 

[Isskint]

I am sure it should just refresh. A couple of thoughts on why not but i do not think it should affect it.

Is the spreadsheet already open by another user?
Is there already an instance of Excel running on your PC (your method will open a new instance of excel and open the spredsheet in it.)

I would amend the automation code slightly and add code to refresh the connection link.

Dim MyXL As Excel.Application
    Dim MyWB As Excel.Workbook
    
    Set MyXL = CreateObject("Excel.Application")
    
    With MyXL
        .Visible = True
        Set MyWB = .Workbooks.Open("C:\Folder One\Folder Two\Folder Three\My_Excel_File.xlsx")
        MyWB.Connections("CONNECTION_NAME").Refresh
    End With

Alternatively you could set the UpdateLinks parameter of the Open Method to TRUE

Function OpenExcelFromAccess()
    Dim MyXL As Excel.Application
    Dim MyWB As Excel.Workbook
    
    Set MyXL = CreateObject("Excel.Application")
    
    With MyXL
        .Visible = True
        Set MyWB = .Workbooks.Open("C:\Folder One\Folder Two\Folder Three\My_Excel_File.xlsx", True)
    End With
End Function

 

[ECEK]

MyWB.Connections("NPCP_DATA")
creates a
Run-Time error '424':
Object required


The second code errors with a compile error User-defined type not defined.
MyXL As Excel.Application

 

[Isskint]

hi

OK my bad i'm doing this on the fly. Connections I think should be applied to the application object, so just

.Connections("NPCP_DATA")

The second error would be no reference set to the relevant Excel library.