K
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
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