Monthly change of query source file

[chriscla]

Hello

I have an external file from which I copy data every month to another existing workbook with a MS query.
Since the file name changes every month (VM 2017 tem 17 01, VM 2017 tem 17 02, VM 2017 tem 17 03, ...) I have to change the connection of the query every time the file name changes. The parameters remain the same. I can do this manually, but some of my colleagues are lost if they have to do this.

So it would be nice to have the query updated based on a date that is entered in a cell:
Cell H1=date entered, i.e. 01/03/2017 (dd/mm/yyyy)
Query update: based on file 'VM 2047 tem 17 03'

Is this possible? Any help more than welcome.

Chris

[MarvinP]

Hi Chris,

In Power Query, an Add-In tool for Excel, this exact problem is solved using what you suggest. Read about it at:
https://social.technet.microsoft.com...rum=powerquery
http://www.excelguru.ca/blog/2014/11...r-power-query/

I'm not sure how I'd do the problem using MSQuery without a sample workbook. I believe doing it using VBA is the only other alternative than using the above linked technique.

[chriscla]

Hi Marvin

The Power Query add-in is not allowed by our IT department, so I have to look out for other solutions .
I've attached a sample workbook to show you what I would like to accomplish.

On the source sheet are the original data (in real this is an external file (VM 2017 tem 17 03.xlsx).
On the result sheet is what I would like to have. I1: date entered. This date should trigger a query or something from the corresponding file based on the month and fill the table.
The source file has over 400 lines and even this number could change if more customers are added.

I hope this makes sense.

Chris

[MarvinP]

Hi Chris,

Is there a chance you could use the Excel Hyperlink function and build a path to the monthly files?

http://www.contextures.com/excelhyperlinkfunction.html

http://chandoo.org/wp/2011/03/31/excel-hyperlinks/

[chriscla]

Hi Marvin

When I insert a hyperlink to the external file, I get a clickable link that opens the file. I don't see a way to get the filtered data in this way. Probably my lack of knowledge .

Chris