Hi,
I am having issues with excel 365 stalling on refresh of queries. It happens periodically and not on the same report every time, so there is not one obviously problematic report.
I have a series of workbooks, each with its own pivot table report, triggered by a daisy chain of windows tasks at certain frequencies (daily/ weekly/ monthly) to open each in a series. VBA auto runs on open to refresh queries, saves as .xlsx, creates an email attaching .xlsx, sends and quits.
At least one report will get stuck on the Excel 365 splash screen (saying connecting to datasource...) and just spins until I wake up and see it is still stuck there (I run reporting early automatically due to lower server traffic).
IF I can intervene by a CTRL+ BREAK, I can rerun the VBA and it won't interfere with the rest of the chain of reports (windows tasks triggered by event of previous windows task completion). But, I frequently find myself having to hit ESC and it stops the data refresh and completes the rest of the VBA to create the email, save as .xlsx, attach and send, then the next report is triggered. I have to wait for the rest of the chain to (hopefully) complete, and run the stalled report(s) manually.
I think what I am looking for is a "trick" to allow excel to open completely, where CTRL+BREAK will always seem to work without fail. Alternatively, I wonder if there are any other "tricks" in excel settings to persist data connections and disallow timeouts. I've had the chain run successfully only a handful of times, so it is possible.
Hi Matt,
What kind of queries, are those power Queries?
Try setting the privacy to None for those workbook query permissions, if they are PQ queries.
Thank you!
They are. I merge a SQL server database query to a Teradata query. In the merge action, it prompts with the privacy settings dialog- I select the "Ignore Privacy Settings" checkbox, but perhaps that is not enough.
How would I navigate to the privacy settings in Excel? Also, can I set them as default? I perform a lot of analytics like this, so it would be useful to "set it and forget it".
Here are the instructions:
If the file is updated by a different user, the permissions are dropped, the new user will be asked to set the permissions and source data credentials if any.
If you're the only user of the file, the permissions will need to be set only once, so it should be fine.
Thank you!
Ok, the Privacy Levels are already set to "None". No other user will refresh, just me (the TD connection requires a litany of access privileges at my org). I refresh and distribute the reports.
The issue still intermittently persists, not on any one specific report. I get the Excel 365 splash screen, but does not open fully. This in and of itself is not really an issue, but if Excel doesn't open completely, I cannot CTRL+BREAK to restart the VBA if the refresh stalls. thinking about an Application.Wait option before triggering the query refreshes.
It sounds like this, but Enable Fast Data Load and Enable Background Refresh are already unchecked.
This is what I basically run for each report. I have the .xlsm refresh and save as .xlsx to be used as an attachment to the email created.
Sub Workbook_Open()
Dim olApp As Object
Dim olMailItm As Object
Dim Fname As String
Dim strbody As String
Set olApp = CreateObject("Outlook.Application")
Set olMailItm = olApp.CreateItem(0)
Application.Visible = True 'Doesn't always work
'insert time waster here to allow excel to open fully (and not stick on excel splash screen)?
'gets email distro and attachment creation data
Sheets("Meta").Visible = True
Sheets("Meta").Select
With olMailItm
Dist = ""
Folname = Cells(1, 1).Value
Subj = Cells(2, 1).Value
For iCounter = 3 To WorksheetFunction.CountA(Columns(1))
If Dist = "" Then
Dist = Cells(iCounter, 1).Value
Else
Dist = Dist & ";" & Cells(iCounter, 1).Value
End If
Next iCounter
Sheets("Meta").Visible = False
Sheets("Stips").Select 'Goes back to main tab
'refreshes queries and pivot tables
ThisWorkbook.RefreshAll
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Folname & Subj, 51
With olMailItm
.Display
End With
'Customize email body message here
strbody = "<HTML><BODY>Hello!<br><br>" & _
"Please see the attached reporting for Master and Walls In migration activities.<br><br>" & _
"Thank you!</HTML></BODY>"
Fname = Folname & Subj & ".xlsx"
'sends email
.To = Dist
.Subject = Subj
.HTMLBody = strbody & "<br>" & .HTMLBody
.Attachments.Add Fname
.Send '.Display
End With
Set olMailItm = Nothing
Set olApp = Nothing
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
Application.Quit
End Sub
Worths to try:
Application.Wait Now()+TimeSerial(0,0,20)
for a 20 seconds delay.
Hard to debug if the error is not consistent though.
ok, perfect. I will give it a try. Thanks for your help! I'll check back after some testing 🙂
