RE: Using ODBC to export to xlsx format

[Pak-Ming Cheung - MSFT]

I can successfully use something like:
"
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
*.xlsb);DBQ=D:\\temp\\ExcelTest.xlsx
"
to connect.

But since the ACE driver is not in-band with windows, please make sure you
have installed the ACE driver at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Thanks,
Ming.
WDAC Team, Microsoft.

P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)

"Pony_Boys" wrote:

> What is the ODBC connection string to export to xlsx format?
>
> I can export to xlsb format using ODBC driver. I have tried all sorts of
> combination stings but nothing works. The only format that ODBC export is
> the xlsb.
>
>
> The Microsoft Web site has the text below about the connection string - but
> - i cannot get this work for xlsx format.
>
> ----
> If you are application developer using ODBC to connect to Microsoft Office
> Excel data, set the Connection String to Driver={Microsoft Excel Driver
> (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file
> ---

[Pony_Boys]

I tried that download and it did not work. I have version 12.0.6211.1000 of
the ACEODBC.DLL file

Here is what is occurring for the three file formats: xlsm, xlsb. xlsx

XLSX: Cannot create new xlsx files - but - can append to an existing xlsx
file.

XLSM: cannot create new files or append to existing xlsm files.

XLSB: Can create new xlsb files - but - cannot append to an existing xlsb fi

[Pak-Ming Cheung - MSFT]

You can try to use the following to create an .xlsx file:

SQLConfigDataSource(
NULL,
ODBC_ADD_DSN,
TEXT("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"),
TEXT("DSN=MyDSN\0CREATE_DB=C:\\test.xlsx\0DBQ=C:\\test.xlsx\0\0")
);

After SQLDriverConnect with the above DSN - MyDSN, it will create an .xlsx
for you.

However, i am not very sure how we can insert data into such an empty
database. Maybe, you already got some code to insert data into it, right? If
so, could you please post it here.