Microsoft Query - refreshing all

cindygo

New Member
Joined
Nov 30, 2010
Messages
40
Hoping someone can help.

Issue:
On a frequent basis a report is run and exported into an Excel format on to the server. The file is always named the same thing, let’s says Hours.xls, and overwrites the existing file to always have the most current data.

We need to view the Hours data in a verity of ways and therefore use pivot tables for that. However I don’t want to re-create them each time a new report is generated. My thinking is to create a second xls that imports the data from the Hours.xls into tab 1. Additional tabs within this second xls would have pivot tables from tab1 in different ways.

Here is what I’ve done. I export an Hours report from the db and posted to the server. 100 records. I created the Second xls called HoursPivot. Via Data > Existing Connections > From Microsoft Query I point to the Hours.xls and import the data. Then I created additional tabs of different pivot tables pointing to tab1. This works great.

I re-run the report from the db, 50 records > export as Excel and overwrite the hours.xls. Open HoursPivot and refresh all tabs. Tab 1 shows 50 records and pivots reflect that.

Re-run the report again from the db, this time 150 records. Overwrite file on server. Open HoursPivot and refresh all tabs. Tab 1 shows 100 records and pivots reflect that. The issue as you can see is that it only viewing the number of records from the original exported report.

Is there a way to have the imported data from a query always import ALL the records from the xls it is pointing too?

Hope that make sense. Thanks for your help,
Cindy
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top