HRESULT: 0x800A03EC setting DataSource for pivot tables.

[katherineolson]

Hi all --

I am trying to query one excel spreadsheet and set this query as the
data source for a pivot table in another spreadsheet using C#. The
code goes like this -- (note, the names have been changed to protect
the innocent :

msQuery = new String[5]

msQuery[0] = @"DSN=Excel Files;DBQ=<datasource excel
file path>;DriverId=790;MaxBufferSize=2048;PageTimeout=30";
msQuery[1] = string.Format("SELECT `'{0}'`.`Column A`,
`'{0}'`.`Column B`, ", sheetName + "$");
msQuery[2] = string.Format("`'{0}'`.`Column C`,
`'{0}'`.`Column D`, ", sheetName + "$");
msQuery[3] = string.Format("FROM `'{0}'`", sheetName +
"$");
msQuery[4] = string.Format("WHERE (`'{0}'`.`Column A`
Is Not Null)", sheetName + "$");

Excel.PivotTable pt = (Excel.PivotTable)xlSheet.PivotTables(<worksheet
name>);


pt.SourceData = msQuery;
pt.RefreshTable();

On the line "pt.SourceData = msQuery;" I get the dreaded HRESULT:
0x800A03EC exception. I have tried changing the permissions for the
asp.net user account and culture info shouldn't be an issue (everything
is en-US).

Entire exception text is:
System.Runtime.InteropServices.COMException was caught
Message="Exception from HRESULT: 0x800A03EC"
Source=""
ErrorCode=-2146827284
StackTrace:
at System.RuntimeType.ForwardCallToInvokeMember(String
memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes,
MessageData& msgData)
at Excel.PivotTable.set_SourceData(Object )
at Generator.ModifyPivotTables(Workbook xlTemplateWorkBook,
String localDataFile) in C:\Generator.cs:line 1534
at ExcelGenerator.Gen

Has anyone dealt with this issue with any success?

Thanks in advance for your help!

Katherine

 

[katherineolson]

I figured I would post the solution The Excel file I was trying to
alter had the pivot table source set at design time to an internal data
source. Interestingly, if you set the data source of a pivot table to
be internal, Excel stores it as a string -- if you set it to an
external data source, Excel stores the query in an array. This
particular instance of the all-to-familiar HRESULT: 0x800A03EC
exception was just the result of a type mismatch. If you want to
update a pivot table data source to an external excel file
programatically using MS Query, you must initialize the file using an
external datasource as well.

Just a tip for any other Excel programmers out there who are interested
-- I ended up dumping MS Query and using OLEDB instead. Much less
pain