Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table
Client Context:
WinXP: 5.1.2600 Service Pack 1 Nu 2600 IE: 6.0.2800.1106 SP1 Excel 10.5815.4219 SP2 Server Context: Win2000 Server+IIS How to reproduce: 1/ From IE 6.0 Click an hyperlink (with query string part) on an asp that returns an excel file containing a PivotTable based on cell range. 2/ Then click on refresh from the PivotTable ToolBar. This work fine with XL2000. May be usefull Asp Code: <%@ Language=VBScript % <% Response.Buffer = false Response.ContentType = "application/vnd.ms-excel" Const adTypeBinary = 1 Dim strFilePath strFilePath = "D:\classeur2.xls" Set objStream = Server.CreateObject("ADODB.Stream") objStream.Open objStream.Type = adTypeBinary objStream.LoadFromFile strFilePath Response.BinaryWrite objStream.Read objStream.Close Set objStream = Nothing % And here is the excel file: <?xml version="1.0"? <Workbook xmlns="urn:schemas-microsoft- com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" <DocumentProperties xmlns="urn:schemas-microsoft- com:office:office" <Author </Author <LastAuthor </LastAuthor <Created2003-12-04T14:48:47Z</Created <LastSaved2003-12-04T15:27:52Z</LastSaved <CompanyDATASET</Company <Version10.4219</Version </DocumentProperties <OfficeDocumentSettings xmlns="urn:schemas-microsoft- com:office:office" <DownloadComponents/ <LocationOfComponents HRef="file:///\\"/ </OfficeDocumentSettings <ExcelWorkbook xmlns="urn:schemas-microsoft- com:office:excel" <WindowHeight8070</WindowHeight <WindowWidth13605</WindowWidth <WindowTopX0</WindowTopX <WindowTopY1230</WindowTopY <ProtectStructureFalse</ProtectStructure <ProtectWindowsFalse</ProtectWindows </ExcelWorkbook <Styles <Style ss:ID="Default" ss:Name="Normal" <Alignment ss:Vertical="Bottom"/ <Borders/ <Font/ <Interior/ <NumberFormat/ <Protection/ </Style <Style ss:ID="s21" <Borders <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ </Borders </Style <Style ss:ID="s23" <Borders <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ </Borders </Style <Style ss:ID="s24" <Borders <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ </Borders </Style <Style ss:ID="s25" <Borders <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ </Borders <NumberFormat/ </Style <Style ss:ID="s26" <Borders <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/ </Borders <NumberFormat/ </Style </Styles <Names <NamedRange ss:Name="a" ss:RefersTo="=Feuil1!R1C1:R2C2"/ </Names <Worksheet ss:Name="Feuil1" <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="7" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60" <Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="24"/ <Column ss:AutoFitWidth="0" ss:Width="20.25"/ <Row <Cell<Data ss:Type="String"a</Data<NamedCell ss:Name="a"/</Cell <Cell<Data ss:Type="String"b</Data<NamedCell ss:Name="a"/</Cell </Row <Row <Cell<Data ss:Type="Number"1</Data<NamedCell ss:Name="a"/</Cell <Cell<Data ss:Type="Number"2</Data<NamedCell ss:Name="a"/</Cell </Row <Row ss:Index="4" <Cell ss:Index="4" ss:StyleID="s21"<Data ss:Type="String"Somme a</Data</Cell <Cell ss:StyleID="s23"/ </Row <Row <Cell ss:Index="4" ss:StyleID="s21"<Data ss:Type="String"b</Data</Cell <Cell ss:StyleID="s23"<Data ss:Type="String"Somme</Data</Cell </Row <Row <Cell ss:Index="4" ss:StyleID="s21"<Data ss:Type="Number"2</Data</Cell <Cell ss:StyleID="s26"<Data ss:Type="Number"1</Data</Cell </Row <Row <Cell ss:Index="4" ss:StyleID="s24"<Data ss:Type="String"Total</Data</Cell <Cell ss:StyleID="s25"<Data ss:Type="Number"1</Data</Cell </Row </Table <WorksheetOptions xmlns="urn:schemas-microsoft- com:office:excel" <PageSetup <Header x:Margin="0.4921259845"/ <Footer x:Margin="0.4921259845"/ <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996" x:Right="0.78740157499999996" x:Top="0.984251969"/ </PageSetup <ValidPrinterInfo/ <PaperSizeIndex9</PaperSizeIndex <HorizontalResolution600</HorizontalResolution <VerticalResolution600</VerticalResolution <Selected/ <DisplayFormulas/ <Panes <Pane <Number3</Number <ActiveRow5</ActiveRow <ActiveCol4</ActiveCol </Pane </Panes <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions <PivotTable xmlns="urn:schemas-microsoft- com:office:excel" <Namept1</Name <HideTotalsAnnotation/ <LocationR4C4:R7C5</Location <DefaultVersion0</DefaultVersion <PivotField <Namea</Name <DataTypeInteger</DataType </PivotField <PivotField <Nameb</Name <OrientationRow</Orientation <Position1</Position <DataTypeInteger</DataType <PivotItem <Name2</Name </PivotItem </PivotField <PivotField <DataField/ <NameDonnées</Name <OrientationRow</Orientation <Position-1</Position </PivotField <PivotField <NameSomme a</Name <ParentFielda</ParentField <OrientationData</Orientation <Position1</Position </PivotField <PTLineItems <PTLineItem <Item0</Item </PTLineItem <PTLineItem <ItemTypeGrand</ItemType <Item0</Item </PTLineItem </PTLineItems <PTSource <CacheIndex1</CacheIndex <RefreshName </RefreshName <RefreshDate2003-12-04T16:20:41</RefreshDate <RefreshDateCopy2003-12-04T16:20:41</RefreshDateCopy <ConsolidationReference <FileNamea.xml</FileName <Namea</Name </ConsolidationReference </PTSource </PivotTable </Worksheet <Worksheet ss:Name="Feuil2" <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60"/ <WorksheetOptions xmlns="urn:schemas-microsoft- com:office:excel" <PageSetup <Header x:Margin="0.4921259845"/ <Footer x:Margin="0.4921259845"/ <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996" x:Right="0.78740157499999996" x:Top="0.984251969"/ </PageSetup <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions </Worksheet <Worksheet ss:Name="Feuil3" <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60"/ <WorksheetOptions xmlns="urn:schemas-microsoft- com:office:excel" <PageSetup <Header x:Margin="0.4921259845"/ <Footer x:Margin="0.4921259845"/ <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996" x:Right="0.78740157499999996" x:Top="0.984251969"/ </PageSetup <ProtectObjectsFalse</ProtectObjects <ProtectScenariosFalse</ProtectScenarios </WorksheetOptions </Worksheet <PivotCache xmlns="urn:schemas-microsoft- com:office:excel" <CacheIndex1</CacheIndex <Schema s:id="RowsetSchema" xmlns="uuid:BDC6E3F0-6DA3- 11d1-A2A3-00AA00C14882" <ElementType s:name="row" s:content="eltOnly" <attribute s:type="Col1"/ <attribute s:type="Col2"/ <extends s:type="rs:rowbase"/ </ElementType <AttributeType s:name="Col1" rs:name="a" <datatype dt:type="int"/ </AttributeType <AttributeType s:name="Col2" rs:name="b" <datatype dt:type="int"/ </AttributeType </Schema <data xmlns="urn:schemas-microsoft-com:rowset" <row Col1="1" Col2="2" xmlns="#RowsetSchema"/ </data </PivotCache </Workbook |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table
Hi Luc,
We would appreciate your patience while we are looking into this issue. We will post our response as soon as we have any update for you. Thanks for posting to MSDN Managed Newsgroup. Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table
Hello Luc,
Thank you for your patience while we researched your issue. Using the information in your post, I was able to reproduce the "Cannot open PivotTable source file..." error. I reproduced the problem both in Excel 2002 and Excel 2003. The problem is caused by Excel's determining the Name of the workbook. If you were to query the FullName property of the workbook object after the ASP streams it down to Excel, you would see that the FullName property contains the querystring. This is causing the confusion for the SourceData property of the PivotCache. I will file a bug report to Excel development so that they can study this problem in more detail and then make a determination if this is something that can be fixed in a future version of Excel. I found a workaround that I believe you will find useful. Instead of using one ASP page as in your example, we will use 2 ASP pages. The first ASP page is the one we call and pass in a querystring. This ASP page then sets a Session variable for each of the querystrings you pass in. Next, it loads the second ASP page, which in turn reads the Session variables and sets its ContentType to be handed down to Excel. This way, there is no "querystring confusion" by Excel when it opens the second ASP page. I have provided 2 sample ASP pages below to illustrate this workaround: Default.asp: <% @LANGUAGE=VBScript % <% ' Create a param1 session variable: Session("param1") = Request.Querystring("ID") ' more session variables could be created here for additional querystrings ' Now open Page2.asp: strRedirect = "Page2.asp" Response.Write("<META HTTP-EQUIV=Refresh CONTENT=""0;URL=" + strRedirect + """") % Page2.asp: <% @LANGUAGE=VBScript % <% sParam1 = Session("param1") Response.ContentType = "application/x-msexcel" Const adTypeBinary = 1 Dim strFilePath strFilePath = "C:\test.xls" Set objStream = Server.CreateObject("ADODB.Stream") objStream.Open objStream.Type = adTypeBinary objStream.LoadFromFile strFilePath Response.BinaryWrite objStream.Read objStream.Close Set objStream = Nothing % From the example above, you call Default.asp passing in the querystring(s). Then default.asp causes Page2.asp to be the one that actually opens in Excel. Since Page2 uses Session variables for parameters, instead of querystrings, Excel can handle the Page2.asp correctly when resolving the SourceData location for the PivotCache. In case some of your clients have Excel 2000, please be sure those clients have the latest Excel 2000 updates to ensure session variables work correctly. For example, make sure those clients have Office 2000 SR1 or later. This is mentioned in the following article: 264143 - FIX: ASP Session Variables Empty When Office 2000 MIME Types Are Streamed with Internet Explorer http://support.microsoft.com/default...B;en-us;264143 Thank you for reporting this issue with Excel 2002. I hope you find the workaround acceptable. For information and sample code for integrating Office with Visual Basic, Visual C++, Internet Scripts, and other programming languages, please see http://msdn.microsoft.com/library/te...SOfficeDev.htm. This site contains the most up-to-date information for using developer tools for Office integration and extensibility. Best regards, Greg Ellison Microsoft Developer Support This posting is provided "AS IS" with no warranties, and confers no rights. Are you secure? Please visit the Microsoft Security & Privacy Center (http://www.microsoft.com/security) for the latest news on security updates. -------------------- From: "Luc Alquier" Subject: OfficeXP "Cannot open pivot table source file" when refreshing pivot table Date: Thu, 4 Dec 2003 07:46:16 -0800 Newsgroups: microsoft.public.excel.programming Client Context: WinXP: 5.1.2600 Service Pack 1 Nu 2600 IE: 6.0.2800.1106 SP1 Excel 10.5815.4219 SP2 Server Context: Win2000 Server+IIS How to reproduce: 1/ From IE 6.0 Click an hyperlink (with query string part) on an asp that returns an excel file containing a PivotTable based on cell range. 2/ Then click on refresh from the PivotTable ToolBar. This work fine with XL2000. May be usefull Asp Code: <%@ Language=VBScript % <% Response.Buffer = false Response.ContentType = "application/vnd.ms-excel" Const adTypeBinary = 1 Dim strFilePath strFilePath = "D:\classeur2.xls" Set objStream = Server.CreateObject("ADODB.Stream") objStream.Open objStream.Type = adTypeBinary objStream.LoadFromFile strFilePath Response.BinaryWrite objStream.Read objStream.Close Set objStream = Nothing % And here is the excel file... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error "invalid data source reference" for pivot table | Excel Worksheet Functions | |||
Excel 2007: "Reference is not valid" when refreshing pivot table | Excel Discussion (Misc queries) | |||
Pivot Table "Data source reference is not valid" error cause? | Excel Discussion (Misc queries) | |||
"Cannot open pivot table source file" | Excel Discussion (Misc queries) | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |