thread68-1457325 xls data import not working

[JanetStackpole]

I found the above thread..and am having the same problem.

I have two xls workbooks, 2003 version, located on a file server.

1. A source xls workbook that refreshes data from SQL Server into a pivot table.

2. A workbook that is used to import the data from the pivot table from the 1st workbook. (data -> import external data -> import data...)

When refreshing the 2nd workbook, I get one of the following results:

On some machines it refreshes and displays all of the data.

On other machines it refreshes without errors, but only displays a one column of data.

In both instances they are accessing the same xls workbook.

I am stumped.. Any assistance would be greatly appreciated.

 

[SkipVought]

Why not use the original query to the SQL Server?

The problem may be that you are querying a PivotTable, where there are BLANK cells in some of the aggregation columns. The query manager looks at a certain number of entries (usually 8 but could be different on various PCs) and determines whether the column's data is TEXT or NUMERIC. If it determines that a column is TEXT, then NO NUMERIC DATA WILL BE DISPLAYED.

So when you say, "but only displays a one column of data" do you aget HEADINGS but no DATA? I sispect that that's the case, expecially since it a PT, where a TEXT value ALMOST ALWAYS appears in the first 8 rows of data for ALL columns.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JanetStackpole]

Thanks your getting back with me.

These have been set up for a while. I tried talking the dept into changing, but they would like to keep it. The 2nd workbook pulls pivot data a number of different source workbooks. Oddly enough, it used to work with no problem. The deptarment recently got new boxes; then the problems began. I did not see differences in settings ... very odd.

It does display one column of data see example below

agency 2005 2006
ABC 93% removes data when refreshed


It does correctly update the one column so it is hitting the source workbook. All values are percentages with no blank cells.

 

[SkipVought]

Here's a small example, illustrating the problem, only its switched: the TEXT is not displayed; numbers are.

Here's the PT
[tt]
Sum of QTY PCT
RC 2008 2009
MGA 186 688
MGB 45 86
MGC 26 399
MGD 17 114
MGG 366 3847
MGH 126 399
MGM 27 72
MGS 58 271
MGT 64 84
[/tt]
and here's the IMPORT...
[tt]
Sum of QTY PCT F3
RC
MGA 186 688
MGB 45 86
MGC 26 399
MGD 17 114
MGG 366 3847
MGH 126 399
MGM 27 72
MGS 58 271
MGT 64 84
[tt]
Notice that the HEADINGS, 2008 & 2009 are MISSING!!!

http://support.microsoft.com/kb/141284

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and after fiddlin' around...
[tt]
F1 F2 F3
a s d
text data
text data
text data
text data
text data
text data
text data
text data
text data
text data
Sum of QTY PCT
RC 2008 2009
MGA
MGB
MGC
MGD
MGG
MGH
MGM
MGS
MGT
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JanetStackpole]

That worked...thank you so much!

 

[SkipVought]

"That" being what?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]