CLR - OLEDB Excel Test

[stanl]

my first .NET AAAAARRRRRGGGGGHHHHH!!!
The code below extends previous posts and IMHO should work. I attached an updated test.accdb and test.xlsx for anyone who cares. test,xlsx was created with Office 2013, but "SELECT * INTO [Excel 12.0;" should be accurate. The SELECT INTO syntax was 'borrowed' from a Bing search as a suggested .NET method to get data from Access to Excel.

Code: Winbatch

 ;Winbatch 2013 - CLR - OLEDB {baby steps}   - Insert Access data to Excel
;
;
;Stan Littlefield July 12, 2013
;////////////////////////////////////////////////////////////////////////////////////////////////////////
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cXLS = Dirscript():"test.xlsx"
If ! FileExist(cXLS) Then Terminate(@TRUE,"Cannot Continue","Missing Excel File: ":cXLS)
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;'
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()
;putting all the eggs in one basket
oCommand = ObjectClrNew("System.Data.OleDb.OleDbCommand","SELECT * INTO [Excel 12.0;DATABASE=":cXLS:";HDR=NO;IMEX=1;].[Sheet1] from [DOW]", oProvider)
;errors here
oCommand.ExecuteNonQuery()
oCommand=0
oProvider.Dispose()
oProvider=0
Exit
 

[Deana]

I currently suspect the query might be the cause. I simplified the query to "SELECT * from [DOW]" and the code runs through to completion.

[stanl]

I currently suspect the query might be the cause. I simplified the query to "SELECT * from [DOW]" and the code run through to completion.

Possibly, but I can perform the Select Into Excel with ADO/COM. Be nice to isolate the specific error in more detail.

[stanl]

This line works:
oCommand = ObjectClrNew("System.Data.OleDb.OleDbCommand","SELECT * INTO [Excel 12.0 xml;DATABASE=":cXLS:"].[DOW] from [DOW]", oProvider)

If you use [Sheet1] it will create Sheet11.. as it is SELECT INTO, not INSERT INTO. Also I believe that parms like IMEX=1 or HEADERS=NO require setting up a command text with parameters. Finally, .xlsx files require the "12.0 xml" when using the ACE Provider. Tricky... eh.
Attached is working script with comments and test files

[stanl]

Here is the same script only with the Command Object broken down. Was permitted to add a CommandTimeOut, and at least a HDR= parameter. Tried to add IMEX=1 but it errors and no matter if I set HDR=Yes or No headers are included in the output. Seems like the CLR has it's own rules and they appear to be good rules. Hopefully this thread will saved others some frustration if they move some COM scripts into CLR that involve Excel.

Code: Winbatch

 ;Winbatch 2013 - CLR - OLEDB: Insert Access data to Excel
;
;USES: Test.accdb (with DOW table with 819 records); test.xlsx (with just sheet1, no data)
;      both created with Office 2013 but in Office 2007 format
;
;REQUIRES: ACE OLEDB Provider (installed with Office 2007/2010)
;          .NET Assemblies 4.0
;
;TESTED: Win7 32-bit
;
;Stan Littlefield July 13, 2013
;////////////////////////////////////////////////////////////////////////////////////////////////////////
;do required files exist
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cXLS = Dirscript():"test.xlsx"
If ! FileExist(cXLS) Then Terminate(@TRUE,"Cannot Continue","Missing Excel File: ":cXLS)
;create Display Window
BoxOpen("Please Wait","Inserting Access Records To Excel...")
;create connection string and initiate Provider
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;'
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()

;issue "SELECT INTO" with Command Object to create Excel Tab via Office ISAM Drivers
;NOTE: the SQL will create a new tab named DOW, but if that Tab already exists either an error will occur
;      or a DOW1 tab will be created.
oCommand = ObjectClrNew("System.Data.OleDb.OleDbCommand")
oCommand.Connection = oProvider
oCommand.CommandText = "SELECT * INTO [Excel 12.0 xml;HDR=No;DATABASE=":cXLS:"].[DOW] from [DOW]"
oCommand.CommandTimeout = 30
oCommand.ExecuteNonQuery() ;no need to return a recordset object
;recover memory before exiting script
oCommand.Dispose()
oCommand=0
oProvider.Dispose()
oProvider=0
BoxShut() ;close Display Window
Exit
;/////////////////////////////////////////////////////////////////////////////////////////////////////////////  
 
 

[Deana]

Thanks for sharing your solution.