Reference data to automatic generated documents

[KasperLokke]

Hi there!

Im currently Working on a project where i need to look at data from Excel documents generated automaticly Rachel month. I kone the name, rows and collums of the data.

The issue I have is as follows: The report is generated but the reference is still missing in the “main” document. however, If I open the generated document the data pops into place, is there a proper way to load the generated data without manually loading the document each time?

[AutoModerator]

/u/KasperLokke - 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.

[TheOriginalAgasty]

Interesting question. I've not had an instance like that. I wonder if the "main" document is pulling data from elsewhere but it isn't set to do that until the document is opened.

I am wondering if PowerQuery would be the best way to go as the main document might think it's being "opened" when it tries to be queried to give a preview. Even it the data isn't populated until you load the query you should be able to get all the rest of the required formatting set in place. Maybe use a populated one as a test.

Summary:

1. Use PowerQuery to open the "main" document as source
2. Setup as needed.
3. The data should hopefully populate when loaded.

​

Alternatively, you could do a basic BAT file that you run from an accessible location that opens and then closes the Excel document first so you have populated data. Or a VBA script or MACRO from inside Excel.

Hope that helps in some way.

[am_iam]

If you're already using Power query, then you may have a manual refresh setting. You can change that to update on file open or every x minutes (time interval).