Excel Import External Data by Date Automatically


Joined
Feb 23, 2017
Messages
1
Reaction score
0
Hi,
I have used Excel for many years, but am just now learning how to build dashboards. I am creating a dashboard in Excel 2010, and have gotten the "Get External Data" part using Microsoft Query to work for automatically importing an Excel spreadsheet daily into the pivot table/dashboard. The problem is that the import automatically updates with the same spreadsheet over and over. This is not what I need for a current dashboard because the files are never overwritten with new data. In order to keep the dashboard current, I need to import a new/different spreadsheet each day using the same naming convention, such as:

data_20170101.xlsx
data_20170102.xlsx
data_20170103.xlsx

The trick is that I need this to work automatically so that the dashboards stay current on their own. I have searched the web quite a bit, but I haven't seen anyone with this exact scenario.

I am guessing that a bit of code needs to be used somehow. I looked at the Connections, Properties, Definition tab, and noticed a Connection String which has the filename hard coded. Would it be possible to add a function or piece of code to this string so that it points to the most current filename in that folder? Or maybe there is a better way?

Any suggestions are welcomed.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top