How can I get a linked Excel table to update automatically?

[Mr. Southern]

Hi. When you say “background,” are you talking about when the Excel file is closed or just hidden?

I want it to refresh the queries and connections without the user opening it.

 

[Mr. Southern]

Does this mean that the assumption is that once a spreadsheet is linked to Access that it needs to be "refreshed" because it's outdated just because it was linked (e.g.) yesterday? A linked spreadsheet always contains the latest data when the db is opened.

If this is about "refreshing" on a continual basis because the spreadsheet changes (e.g.) every 5 minutes, then there's nothing to do from the Access side of things AFAIC, because Access cannot save a workbook that is already opened by someone else. Excel workbooks are opened exclusively, no?

I'm not sure everyone is on the same page.

The spreadsheet changes every couple of hours during the day. This workbook only has one user and it wouldn't be opened by someone else.

 

[theDBguy]

I want it to refresh the queries and connections without the user opening it.

Hi. We're still not sure if we're on the same page here. Without opening it could mean something that's you don't want something "visible" to the user. In other words, it could be open but hidden from view. Otherwise, how can you expect something to "work" or do its job without opening it (visible or hidden)? Codes can only run when they are executed. If you don't open the file to execute the code, the code won't run. Again, "opening" the file could simply mean using an external code to run the subject file, without user intervention.

 

[Mr. Southern]

Hi. We're still not sure if we're on the same page here. Without opening it could mean something that's you don't want something "visible" to the user. In other words, it could be open but hidden from view. Otherwise, how can you expect something to "work" or do its job without opening it (visible or hidden)? Codes can only run when they are executed. If you don't open the file to execute the code, the code won't run. Again, "opening" the file could simply mean using an external code to run the subject file, without user intervention.

I understand what you're saying. I know it has to open but I don't want it to be "visible" to the user.

 

[Deleted member 147267]

I know it has to open but I don't want it to be "visible" to the user. Yesterday 02:08 PM

Then if the code you posted is OK otherwise, just add

objXL.Visible = False

after

Set objXL = CreateObject("Excel.Application")

Should make it invisible to the user, but of course, this won't affect the workbook if it is already open.

 

[The_Doc_Man]

Just to step in to amplify Pat's last comment: In fact, it is contrary to the model by which Windows MUST run if it wants to sell systems to the USA federal government. There is a principle of "isolation" that means that external non-privileged processes MUST NOT be able to see the contents of memory for another process. This would not only be a security violation but it would disqualify use of Windows for any installations with "Sensitive" or higher data levels. In essence, it PAYS Microsoft to assure that Windows does not ever gain this ability.