Get data from Excel file using query if part of the filename changes regularly.

[randall78]

Hi,

Does anybody know how to use query to get data from another Excel file who's name changes regularly.

The file is always in the same location, but the filename changes on a daily basis.

ACTIVE 01022021.xls
ACTIVE 02022021.xls
ACTIVE 03022021.xls

And so on.

There is only ever one file containing the word ACTIVE, the word ACTIVE gets replaced with ARCHIVED.

[Kenneth Hobson]

Record your query and then modify the macro. The file name can be created based on current date. e.g.

Please Login or Register  to view this content.

[randall78]

Hi,

Sorry but I think you may have misunderstood the question.

I require using query to retrieve data from another workbook, but the name of the workbook changes regularly.

Can I change the filename in the advanced editor and use wildcards (*ACTIVE*.xls)

[Kenneth Hobson]

Misunderstanding would not be the first time for me. I can only guess at what is actually being asked without full details. When seeking help, sometimes you get the best help if you can attach a short example file(s).

I don't know what advanced editor you are talking about. I think that you are talking about modifying a manually built Query. Using wildcards is not going to solve the problem. Manual queries are limited to the built query. I guess you could manually edit it each time.

People mean different things for query so the recorded macro needs to come from you or in a simple attached file or the manual query stored in an attached file. It is hard to help someone without exact details.

I proposed a complete and dynamic solution that actually works. After re-reading post #1, it looks like you need the ddmmyyyy date format. That is easily done.
MsgBox "ACTIVE " & Format(Date, "ddmmyyyy") & ".xls"

There are many ways to get data from other Excel files. QueryTables, ADO, Excel4, etc.

[randall78]

Hi,

Here is a screenshot that i hope will explain.

Attachment 720454

[JeteMc]

I feel that you are talking about Power Query (Get & Transform)
If so then I believe that you can use the filter on the Name column header to select Text Filter > Contains > type ACTIVE (I believe that it is case sensitive)
Here is a video showing how to only select files with certain extensions (beginning at 9:42) which may be helpful also.
https://www.youtube.com/watch?v=_csX8sCzJd0
By the way selecting Attachment 720454 results in the message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
Let us know if you have any questions.