GetPivotData Returning #REF! When PT Based On Imported Data

[AnotherHiggins]

I hope I'm just missing something simple after a long day, because I didn't find anything about this being a known bug....

I have a table that is imported by Excel from an Access database (Data > Import External Data > New Database Query).

That data is in columns A:X. There are an additional 9 columns (Y:AG) of formulas in Excel. I'm thinking that some of the formulas were too long to complete in Access, and I know some of them use functions that Access doesn't recognize, so I think I'm stuck with having these formulas in Excel. But that's not the issue right now....

I have a dynamic Named Range that refers to the imported table. I have a Pivot Table using the Named Range as the source (I've also tried selecting the range directly).

I'm trying to use a series of GetPivotData functions to get the data in a chart-friendly format (They don't want to use a Pivot Chart). Here's the weird part....

No matter what I do, I get #REF!

I'm looking at the Pivot Table - it has data in it. If, in a cell outside the table, I type in "=" and then click on a populated cell within the Pivot Table it auto-generates a GetPivotData function as I'd expect, but the result is #REF!.

If I instead type in "=C7", that returns a value. But pressing "=" and then clicking in C7 returns #REF!.

I've tried copying the query and pasting it into Excel (instead of importing data) and it works as expected. The only difference I can see is that one table is imported and the other is pasted.

Any ideas?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Are you running the QueryTable.Add method each time? NOT!!!

You have MULTIPLE Querytables, I deduce.

Use the Refresh Method to get new data.

Also the QueryTable table name is Sheet Specific. You can have multiple Query_from_Access QT's in a workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Yeah, I'm just refreshing after the initial setup. For the time being at least, I only have one imported table.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

Just noticed it was YOU.

Could it be that a PivotItem is missing, that is in your formula ref? Just a wild shot.

I'm off to run some test to try to replicate this behavior.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

heh. no problem, Skip. I appreciate your help as always.

I don't see how anything could be missing. It's not like I'm trying to type in the arguments myself. I just click on any cell in the Pivot Table and it returns the error.

I'm (finally) heading out for the night (8PM local). I'll try to pare down a reasonably-sized example to post tomorrow.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

No joy here , yet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Very odd John - can't replicate on a simple test

Can you give a description of the layout of the PT? Is it a complicated / multi group beast?

Thought it might be to do with a #N/A or #REF! error in your formulae to the side of the external data but that doesn;t seem to affect GPD on my setup...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question