Open an Excel doc from within Access Refresh problem (1 Viewer)

ECEK · Jun 7, 2016

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.

Code:

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 · Jun 7, 2016

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.

Code:

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

Code:

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 · Jun 7, 2016

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 · Jun 7, 2016

hi

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

Code:

.Connections("NPCP_DATA")

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

Open an Excel doc from within Access Refresh problem (1 Viewer)

ECEK

Registered User.

Isskint

Slowly Developing

ECEK

Registered User.

Isskint

Slowly Developing

Similar threads

Users who are viewing this thread