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