Are you pulling the data using vba or the net query? If you're using the built in query, create a macro enabled workbook and record a macro of the initial setup, then assign the code to a button so it only runs when pressed.

/u/JohnnyBrillcream - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

In Excel, you can set up a global connection to a data source that can be used by multiple queries. To do this, you typically create a connection using the "Get Data" feature and then use that connection as the source for multiple queries. Here's a general outline of the steps:

1. Create a Global Connection:

   • Go to the "Data" tab in Excel.
   • Click "Get Data" and select "Get Data" from the drop-down menu.
   • Choose the type of data source you are connecting to (e.g., database, web, file).
   • Configure the connection to your cloud setup and save it as a connection file. Ensure it's set up to your desired specifications.

2. Create Queries Using the Global Connection:

   • After creating the global connection, you can use it as the source for multiple queries.
   • When creating a new query, select the option to use an existing connection or connection file, and choose the global connection you created in step 1.

3. Edit the Global Connection:

   • If you need to make changes to the connection settings, you can edit the global connection you created. Any queries using that connection will automatically reflect the changes.

By using a global connection, you can maintain a single point of control for your data source settings, which simplifies the process of managing connections in Excel. However, you will still need to update the global connection when your cloud setup changes, but this change will apply to all queries using that connection.

/u/prtzle - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Are you on 32 or 64 bit version? I had this problem using 32

I don't see why you wouldn't use the simple option as you already described of an on open event such as:

Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
End Sub

You state a more elegant way but really this is all there is to it. It should take only a few seconds and it's finished.

Well

• I typically make a button which calls the refresh of each PQ table and PivotTable in the right sequence.
• I ALWAYS add a column to PQ loaded tables to capture the LastQuery date/time (=DateTime.LocalNow() ) and then
• I use that date to remind me to refresh by using conditional formatting to highlight when dates don't match or when dates are older than a specific threshold. I bright yellow highlight the cell the button is in (enlarged cell) to hint it needs pressing...

If you are stuck in this situation, do everything you can to get cleanest data as you can.

If the excel/ csv files are being emailed to you, you could use powerautomate to save the files into sharepoint/cloud. Then do as much cleaning/ merging in power querry.

Yep highly common in every big or small company I’ve come across.

Keep files in SharePoint

Use from web to connect to single files

For consolidating multiple matching files Use from SharePoint folder and the File.Contents trick to speed refresh

Videos 1 and 3 in this playlist Power Query and SharePoint / OneDrive https://www.youtube.com/playlist?list=PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un

Schema changes are going to drive you nuts. And they will happen

Edit: if this is not a sheet a department is using as a psuedo database just pull from a folder (directory it's dumping to) and then you'll need some process to clear the contents from the folder each day or identify/ filter incoming records (if this process occurs daily)

Best practice for excelbased reports: Don’t

Get your business to push to build a data warehouse. Don’t use excel.

Sheets supports opening excel files, just be aware that sheets and excel have different syntax for some formulas. Sheets to Excel also isn't perfect, but excel to Sheets should be no issue.

/u/No-Performer-4174 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

save the excel sheet to your google drive and open it with sheets?

i do this, often.

>My concern is that any changes in the excel file will also change the access database

no it won't, that's not how it works.

This sounds like a solid project! To avoid accidental changes to your Access database, try connecting Excel as read-only using Power Query or linked tables — that way, it only pulls data in without updating anything unless you specifically allow it.

For updates, use a separate sheet or form, so changes are intentional and easier to manage. You can also automate emails using VBA and just make sure to refresh your data regularly to reflect any changes in Access.

Breaking the process into clear steps across separate sheets will make it much easier for your team to follow and reduce confusion. You’re definitely on the right track!

Show your #2 details in the email so they don't have to enter the WB to view, then have your #3 be a different WB that Access reads from.

If I read correctly, you do not want work done in the Excel file to feed into the database.

If it's an MS Access database, you can set properties on the connection string so that users always open the database in Read-Only mode.

If you are considering something like SQL Server, user permissions can allow viewing but not updating data.

One alternative solution, it’s not cheap but you can have a sql warehouse which gives you a jdbc connection and write a vb macro to query the data and drop into a data sheet.

As ever.. what’s the use case?

As someone who loves Excel, I have to comment on this one.

The answer is simple: Power Query. This is the way.

https://learn.microsoft.com/en-us/power-query/connectors/databricks

You can write a Python script to run in a notebook or job and write the results to an Excel file or CSV.

Never tried this, I know this isn't automated, but stops you from having to download.

Connect to Databricks from Microsoft Excel | Databricks Documentation https://share.google/qDPKXWdTLtCFZNjFV

Again, not as good as the other comment about using Python