OLE - Access to Excel Converter

Keywords: 	 access to excel converter OLE

I apologize in advance if you get bored reading this post until the good part at the end. I have finally achieved an OLE DB Transfer methodology that even according to Microsoft Documentation is not supposed to be possible.

First, the problem: With Microsoft DAO, it was pretty simple to export tables or queries from ACCESS to EXCEL - you simply had to invoke the DoCmd Object, then execute TransFerSpreadsheet(). Unfortunately, all that is missing from OLE DB, and there have been complaints from users that the Microsoft stratgey is to eliminate support for ISAM's (or flat files, including dBASE, ParaDox, .wks, Excel, Foxpro ). A second problem is that even using TransFerSpreadsheet(), a physical Table or Query must exist in the Access Database prior to translation. According to the DOCS - "you cannot use TransferSpreadsheet() with an on-the-fly SQL Statement.

My Situation: I am moving more from applications to data-mining scripts written in Winbatch . Recently, there have been major changes in telecommunication rate structures, and therefore the focus of my analysis has changed. To accomplish this, I no move data from DB2, Oracle and DBF files into temporary Access Databases/tables. Why? because I am guaranteed the functionality of Jet 4.0 OLE DB.

The Users: could care less about Access or FW, they want their queries in Excel, where they can sort,graph,report and analyze the raw data to suit their own needs. Even new hires in our department can do more with Excel (in terms or presenting data) than I can. Therefore, it is pointless for me to re-invent that wheel.

So, I have this 'on-the-fly' Access Database created - and need to perform SQL queries to send different results to up to 50 users in Excel. Using DAO (which would work) would be a nightmare!!!

I always felt that the SELECT INTO... command was the answer, but everything I tried failed with OLE Exception errors. At last I got it.

Assume:

ACCESS Database = C:\TEMP\TEMP.MDB
Table = TEMP
Desired user Excel file is C:\TEST.XLS

so in OLE DB

DB := ObjectOpen("ADODB.Connection")
cConn := "Provider=MicroSoft.Jet.OLEDB.4.0; Data
Source=C:\TEMP\TEMP.MDB"

to create a spreadsheet only involves two lines of code

DB.Open(cConn)

RS := DB.Execute(cSQL)

so what is cSQL???

cSQL:= "SELECT * INTO [Excel 8.0;DATABASE=C:\TEST.XLS].[TEST] FROM [TEMP]"

where the .[TEST] = the worksheet name (it could be "Sheet1" )

change Excel 8.0 to dbase III, or Text and you can output your data to DBF, comma-delimited or SDF. When working with Text output, ACCESS looks for a file called schema.ini so if you create one like

[MyFile.TXT]
FORMAT=DELIMITED(|)

your get comma-delimited output, or

[MyExport.DAT]
FORMAT=FixedLength
Col1=MyCustomers Text Width 15
Col2="My Address Field" Text Width 30

can completely customize the output.

Finally, the SQL is not limited to a full table - WHERE clauses, GROUP BY, PIVOT can all be used.

stan littlefield

Article ID:   W14901

File Created: 2001:11:08:12:40:56

Last Updated: 2001:11:08:12:40:56

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Database Search

TechHome

OLE - Access to Excel Converter