Query not retrieving all data from pivot table.

Question

It will not pull any data with a 2010 date. Won't get anything past 3/12/2009. Strange. I have records from 2008 to date. I am using Excel 2007. Very frustrating problem

Answer 1

From Jeff by email:

>At the Query Wizard, I select Excel Files; then the tradelog file. Then at choose collumns I expand the _xlnm#->FilerDatabase and select buy date or guru (any of them) and then select Preview Now. From there, you can see that it >dose not return all the data. I know I want all colunms in the query but this quickly shows not all are being pulled.

Response:

Personally I don't use Microsoft Query to get data from Excel files.

For data that I want to query I use a database to hold the data rather than a worksheet.

I have found MSQuery difficult to use and it often causes problems when, as I have, there are multiple versions of Office on the same machine.

When I tried to create a MSQuery query from your file I got a message saying "this file contains no visible tables".

At one stage it offered me FilterDatabase as the only named range available for me to connect to.

_FilterDatabase is a hidden name that was set up earlier in your use of the sheet and it refers to A3:M107

Thus any data after row 107 is unavailable to this query.

I think this may be what you are connected to.

In order to create a successful query I had to first delete the 2 rows above the data so that the table started in row 1.

And then in MS Query I had to click Options and check Systems Tables.

This let me select from a small range of possible names.

The one I chose was "Trade Log$" which represents the whole of the Trade Log sheet starting at A1.

That worked and refreshed successfully but included all 2000+ rows that are used in the sheet.

So all in all, I suggest you:

• delete the top 2 rows
• delete the excess rows and columns beyond the end of the data
• redo the query, connecting to "Trade Log$" (which appears as a System Table)

Hopefully you will then be OK.

However, I imagine there is probably a better way to achieve what you are trying to do than using MSQuery to access one Excel workbook from another.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 2

You took on a tough one. I am a Excel novice. The file is a history of my stock trades. I have entered all the data myself into a pivot table. I want to use Query to pull reports.

Ex: sort by Guru "X" option trades for the year 2010. Or sort by account....simple stuff.

All the settings on Excel should standard as package.

It will not return any thing past Mar 2009, about 118 lines of data. I have 663 total lines of data and growing.

Thanks again for your help.

Answer 3

What is the source data for the pivot table?

If it is in a worksheet, is the source set correctly to include all the records?

If it is an external query, is the query returning all the records you expect?

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 4

Thanks for your reply, but that did not help. Select all is checked.

This is very fustrating. I have two years of data and can not mine it.

Answer 5

Make sure you have not filtered out the data for later dates

• select the dropdown for the date field in the pivot table > Date Filters > Clear Filter
• also ensure (Select All) is checked

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk