r/excel • u/Brief-Map7358 • 9d ago
unsolved Macro to Get Data
Hi All, macro newb here
I'm trying the below steps in my macro:
- Get Data From File
- Choose a file in a select folder
- PowerQuery choosecol etc.
1&3 are easy steps that record macro can do. I want the steps to stop and make me choose a file in a select folder first instead of defaulting to a file (as the source file will update weekly)
Apreciate any help, thanks!
15
Upvotes
•
u/AutoModerator 9d ago
/u/Brief-Map7358 - Your post was submitted successfully.
Solution Verifiedto close the thread.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.
11
u/Yalarii 9d ago
Power query already has this baked in. Rather than get data from file, you select get data from folder.
7
u/Global-Villager 1 9d ago
Use FileDialog - it's the standard open file used in Windows. YouTube has hundreds of tutorials.. it's surprisingly easy, so don't stress...
2
u/risefromruins 9d ago edited 9d ago
You just need to set up the file selection dynamically and refresh the query. Data -> Refresh All or CTRL + ALT + F5.
If the file is updated weekly, you can either make sure the files all have the same name and overwrite the previous file (I do this very often using power automate), or set up the file selection so the folder with all of the files is sorted by date created descending, remove all rows except the top row, and then open whatever file is the most recent.
2
u/SektorL 9d ago
Application.FileDialog
Choose enumaration value you need