SQL Statement to XLS

[defenderjim]

Hello All,

Sorry if this is not in the best group.

I am trying to create an XLS file using SQL statements in a C++
program.

All works fine, except that each table I create is on a different
worksheet.

I really need them to be all on one worksheet.

Any suggestions?

Thank you.

-Jim



Code roughly as follows:

CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";

CString sExcelFile = "c:\\excelfilename.xls";

sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s
", sDriver, sExcelFile, sExcelFile);

int dbOpen;

try {
// Create the database (i.e. Excel sheet)
dbOpen = database.OpenEx(sSql,CDatabase::noOdbcDialog);
}
catch( ... ) {
// TRACE1("Driver not installed: %s",sDriver);
}

if ( dbOpen ) {
for (short x = 0; x < nTables; x++) {
sSql.Format("CREATE TABLE myTable%d (val1 NUMBER, val2 NUMBER, val3
NUMBER), x);

try {
database.ExecuteSQL(sSql);
}
catch( ... ) {
}

sSql.Format( "INSERT INTO myTable%d ( val1, val2, val3) VALUES (%d,
%d, %d)", x, val1, val2, val3);


try {
database.ExecuteSQL(sSql);
}
catch( ... ) {
}
}
}

 

[Douglas J. Steele]

I think you'll have more luck in a newsgroup related to C++.

This one's for questions about Access, the database product that's part of
Office Professional, which uses VBA.

 

[John Nurick]

Hi Jim,

1) I don't know whether this is possible via ODBC.

2)It has long been possible via DAO (and I believe ADO) by executing a
SQL query using syntax like this for the tablename:

[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[SheetName$A4:E99]

but the ability to create and update tables this way was removed for
legal reasons in Office 2003 SP2 and the most recent update to Office
XP. (See http://support.microsoft.com/kb/904018. Or google for
Amado Access Excel Microsoft
to learn more than you want to know.

3) You can always use Automation to control Excel and explicitly create
the workbook and worksshets, then open a series of recordsets containing
the data for each of the tables you want to export and use Excel's
Range.CopyFromRecordset to place each table in the desired location on
the sheet.

 

[defenderjim]

John,

3) You can always use Automation to control Excel

Well, that worked very nicely.
Thank you for your help.

I am having a problem with this solution,
after the file is correctly written, my program crashes in ole32.dll:

---------------------------
Microsoft Visual C++
---------------------------
Unhandled exception in AnalyzeOutputFiles.exe (OLE32.DLL): 0xC0000005:
Access Violation.
---------------------------
OK
---------------------------


If the file is already open in Excel, then I do not get this crash.


Any ideas?

-Jim