• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Automated VBA stalling- Excel 365|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Automated VBA stalling- Excel 365|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosAutomated VBA stalling- Excel 365
sp_PrintTopic sp_TopicIcon
Automated VBA stalling- Excel 365
Avatar
Matt Beumer

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
1
February 26, 2022 - 6:15 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
February 28, 2022 - 11:38 pm
sp_Permalink sp_Print

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.

Avatar
Matt Beumer

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
3
February 28, 2022 - 11:55 pm
sp_Permalink sp_Print

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". 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
March 1, 2022 - 12:09 am
sp_Permalink sp_Print

Here are the instructions:

https://support.microsoft.com/.....ee7900b540

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.

Avatar
Matt Beumer

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
5
March 1, 2022 - 1:48 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
March 1, 2022 - 2:00 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Matt Beumer

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 26, 2022
sp_UserOfflineSmall Offline
7
March 1, 2022 - 2:20 am
sp_Permalink sp_Print

ok, perfect. I will give it a try. Thanks for your help! I'll check back after some testing 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Henseler
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6210
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.