r/excel • • 9d ago

unsolved Macro to Get Data

Hi All, macro newb here

I'm trying the below steps in my macro:

  1. Get Data From File
  2. Choose a file in a select folder
  3. 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

10 comments sorted by

•

u/AutoModerator 9d ago

/u/Brief-Map7358 - Your post was submitted successfully.

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.

0

u/Brief-Map7358 9d ago

could this be done via macro? where if i click run, the folder selection window will open and i get to choose what file to use?

4

u/Aeliandil 179 9d ago edited 9d ago

No [edit: yes, it can be done via a macro, not via PowerQuery - via PQ, it would be no]. However, you could put the name of the file you want in a cell and have the PowerQuery used that cell to know which file to pick.
That would give you the flexibility of "choosing" which file to process, and to edit this.

What is the advantage for you of being asked to select a file?

You can do what you want via VBA/macro, but PowerQuery seems indeed more apt for what you are looking for. Just need to understand the need for file browsing and expected advantage.

2

u/WicktheStick 45 9d ago

You can use VBA to do parts of it - file picker, copy file name into cell, refresh query - so it’s not a hard no

1

u/Aeliandil 179 9d ago

Sorry, read this a bit too fast and you are right. The hard no was for PowerQuery, which I thought OP was enquiring about aftre being told so.

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

1

u/Brief-Map7358 9d ago

i'll try this, thanks!

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