Database Search

TechHome

• CLR Scripts to Create Linked Servers
• Create an XML Schema File from a SQL Server Database
• Get Database Results into a WIL Array and Display
• Insert Access Data to Excel
• Insert Excel Data Into Access
• OleDb GetSchema
• OleDb

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Insert Access Data to Excel

 Keywords: Insert Access data to Excel dotNet ACE OLEDB Provider Microsoft.ACE.OLEDB.12.0 System.Data.OleDb.OleDbCommand ExecuteNonQuery

;/////////////////////////////////////////////////////////////////////////////////////////////////////////////   
;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
;/////////////////////////////////////////////////////////////////////////////////////////////////////////////   

Article ID:   W17812

Filename:   Insert Access Data to Excel.txt

File Created: 2013:07:15:08:19:24

Last Updated: 2013:07:15:08:19:24