Solved Open Excel workbook, but have WorkBook_Open code run

[Gasman]

I have an Excel workbook, that reads data from an Access query and produces a few charts.

Up until now I have been opening it manually, but I decided to add the opening of it to my Blood Pressures form.

It opens fine, but I either get a message that this will Cancel the Refresh (which I want) or does nothing. A manual RefreshAll then does not run the code which copies formulae for me. Code is below.

Is there a way to just run the WorkBook_Open() code from Access, without having to create a class, which is all ChatGPT can offer.

TIA

Private Sub Workbook_Open()
Dim lngLastRow As Long, lngLastRowCopy As Long
Dim strColName1 As String, strColName2 As String
Dim iCol As Integer
'Refresh sheet to get latest data.
ActiveWorkbook.RefreshAll
'Get last row in A in sheet qryPressure
Sheets("qryPressure").Select
'Find colname for 'With Time'
iCol = Application.WorksheetFunction.Match("With Time", ActiveSheet.Rows(1), 0)
strColName1 = ActiveSheet.Cells(1, iCol).Address(False, False)
strColName2 = ActiveSheet.Cells(1, iCol + 3).Address(False, False)

'Allow for wide sheets
If Len(strColName1) > 2 Then
    strColName1 = Left(strColName1, 2)
    strColName2 = Left(strColName2, 2)
Else
    strColName1 = Left(strColName1, 1)
    strColName2 = Left(strColName2, 1)
End If
lngLastRow = getlastrow(ActiveSheet.Name) '.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lngLastRowCopy = getlastrow(ActiveSheet.Name, strColName1) 'Just copy from last row available instead of row 2 as before
'Copy formula in F2 down.
Range(strColName1 & "2:" & strColName2 & "2").Copy
Range(strColName1 & lngLastRow - 10 & ":" & strColName2 & lngLastRow).PasteSpecial xlPasteFormulasAndNumberFormats
Range(strColName1 & lngLastRow).Select
Sheets("Weekly Chart").Select ' Change as required
ActiveWorkbook.Save
End Sub

Code to open it.

Private Sub cmdPressure_Click()
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim excelFilePath As String

    ' Path to your Excel file
    excelFilePath = Environ("HomePath") & "\Documents\Blood_Pressures.xlsm"
   
    ' Create a new instance of Excel
    Set xlApp = CreateObject("Excel.Application")
    'Getting refresh error when opening workbook from here

    xlApp.DisplayAlerts = False
    xlApp.Visible = True

    ' Open the workbook
    Set xlWorkbook = xlApp.Workbooks.Open(excelFilePath)

    ' Make Excel visible
    'xlApp.Visible = True
    xlApp.DisplayAlerts = True
    'xlWorkbook.RefreshAll

     'Optional: Clean up if you don't need the Excel objects later
     Set xlWorkbook = Nothing
     Set xlApp = Nothing

End Sub

 

[Isaac]

I'm a little confused. Your process should work just fine, I've done this a lot - open the Excel from access vba, and include your excel refresh code either in the Workbook_Open portion......Or continue automating excel from inside the access vba (which is what you're doing in the commented out line).

Can you clarify at exactly which point in your current code you get "this will cancel the refresh" ?

 

[Isaac]

Oh - make sure the applicationobject.usercontrol=true

 

[Gasman]

If I comment out the error display override I get

which of course is what I want to happen.
I tried putting a breakpoint on the workbook Open event, but it does not stick, and I just get the MSGBOX message I place at the end of the Open just to confirm it ran through.

According to ChatgPT just using the workbook.open command should run the code, which it does do, but balks at the refresh.
Now if I could just run the Open event from Access after opening it, that might work?, but I could not get the syntax correct.

I added xlApp.UserControl = True just before the Open workbook command, but no difference?

 

[cheekybuddha]

Try passing True to the UpdateLinks parameter.

learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

 

[Gasman]

Try passing True to the UpdateLinks parameter.

learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open

I tried with 3 ?, no difference.

 

[Gasman]

I do not believe I can do the refresh from access, as I need that to happen first, so the code can pick up the new data and copy formulae that depends on the new data.

 

[KitaYama]

@Gasman
I was in a hurry and haven't read in details. I'm sorry if I'm misunderstanding the question.

Excel gives an option to automatically refresh the linked data/tables when the file opens.
You don't need to refresh your table manually from Access.
In excel, click the table linked to your Access table, select Data tab in ribbon, in "Queries & Connections" group, click "properties", then there's a small icon in front of query name. Click it.
The query property opens. Then set the following option.

 

[Isaac]

If I comment out the error display override I get
View attachment 119549
which of course is what I want to happen.
I tried putting a breakpoint on the workbook Open event, but it does not stick, and I just get the MSGBOX message I place at the end of the Open just to confirm it ran through.

According to ChatgPT just using the workbook.open command should run the code, which it does do, but balks at the refresh.
Now if I could just run the Open event from Access after opening it, that might work?, but I could not get the syntax correct.

I added xlApp.UserControl = True just before the Open workbook command, but no difference?

I think it means an active refresh is already in process, because you have to configure your Data Connection to NOT refresh when the workbook opens. It's one of the options.

 

[Isaac]

oops, I see KitaYama already beat me to it.

 

[Gasman]

@Gasman
I was in a hurry and haven't read in details. I'm sorry if I'm misunderstanding the question.

Excel gives an option to automatically refresh the linked data/tables when the file opens.
You don't need to refresh your table manually from Access.
In excel, click the table linked to your Access table, select Data tab in ribbon, in "Queries & Connections" group, click "properties", then there's a small icon in front of query name. Click it.
The query property opens. Then set the following option.

View attachment 119551

Thank you for the reply.
I had not mucked around with those settings, did not even know they existed.
I have

So I thought I would let that setting do it and not in my code, so commented it out, but still does not refresh. It still does not do it from Access but again OK if opened manually.
I will live with opening it manually.

 

[KitaYama]

I will live with opening it manually.

One last try please.
Can you open your excel file this way?
This method opens the file and refresh it.

Private Sub cmdPressure_Click()

    Dim excelFilePath As String

    excelFilePath = "Full Path To Your Excel File"
   Call Shell("explorer.exe """ & excelFilePath & """", vbNormalFocus)
 
End Sub

Edit: Based on the amount of data, sometimes after the excel file opens, it needs a little while to be synced.
Check the progressbar in Excel's taskbar. Wait for it to be finished.

Also double check The option I mentioned (refresh data when Opening the file) is ticked.

 

[cheekybuddha]

I suggest you ask this at UA.

Debaser will be able to give you a definitive answer as to whether it's possible or not.

 

[Gasman]

I suggest you ask this at UA.

Debaser will be able to give you a definitive answer as to whether it's possible or not.

Ok, will try, I know he is a bit of a wizard with Excel.

 

[Gasman]

Got it.

Removed Background Refresh from the query.

 

[CJ_London]

I tried putting a breakpoint on the workbook Open event, but it does not stick

What about using stop?

 

[Gasman]

What about using stop?

Never thought of that :-(
Something I have never used.

 

[cheekybuddha]

For completeness: Cross-posted at UA

 

[CJ_London]

there is also debug.assert

 

[Gasman]

there is also debug.assert

My memory is rubbish, but I will try and remember both, if there is a next time.