Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: access to excel converter OLE
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:
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 30can 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