HRESULT: 0x800A03EC setting DataSource for pivot tables.

[[email protected]]

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

[[email protected]]

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

[email protected] wrote:
> 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