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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,141,224
Messages
5,705,122
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top