[AccessD] Export to multiple Excel sheets

Thank you Drew, Lambert & Marty.

I've got to go waste time in a meeting now, but I'm going to try Drew's 
approach since it worked for him. I may have another question or two ...

Gina


From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem 
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 13:58:52 -0600

Hehehe, I must be bored today, cause I decided to try it out.  This works
PERFECTLY!

Okay, I dumped a table into a blank database.  The table is called
tblRequests.  It's the IS Request table that stores user requests for our
company.  To see if it worked, I was going to push the records out to an
Excel file.  I made a blank excel file, and removed all the sheets but one
(wouldn't let me remove all of them....).  Then I wrote the following
code...technically, this code can be run from anything (with either VBA or
VB).

Function ExcelConnection(ExlCnn As ADODB.Connection, strFile As String)
'This function just makes an ADO connection to an excel file....the extended
properties is the only trick
Set ExlCnn = New ADODB.Connection
With ExlCnn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Properties("Extended Properties") = "Excel 8.0"
     .Open strFile
End With
End Function

Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim cnnAcc As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cnnAcc = New ADODB.Connection
With cnnAcc
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open "D:\requests.mdb"
End With
ExcelConnection cnn, "D:\Book1.xls"
Set rs = New ADODB.Recordset
strSQL = "SELECT Requestor FROM tblRequests GROUP BY Requestor"
'Now going to open a recordset to the table, to get a list of the
requestors.....
rs.Open strSQL, cnnAcc, adOpenKeyset, adLockReadOnly
rs.MoveFirst
'Now, I'm going to loop through the requestors, and push the data out to
Excel
Do Until rs.EOF = True
     strSQL = "SELECT TicketNumber, RequestDate, RequestType, 
RequestSubType,
FullDescription INTO " & _
     rs.Fields(0).Value & " " & _
     "FROM tblRequests IN ""D:\requests.mdb"" " & _
     "WHERE Requestor=""" & rs.Fields(0).Value & """"
'Note, I'm selecting fields from the Access table, pushing it INTO a table
named for the Requestor (Field 0 of
'the recordset object), and I'm telling to pull the records FROM tblRequests
IN an external Access .mdb.
'Since the next line uses a connection object connected to an Excel
Worksheet, it will create that 'table' as
'a new sheet in the Excel Workbook
     cnn.Execute strSQL
     rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
cnnAcc.Close
Set cnnAcc = Nothing
MsgBox "Done"
End Sub

Worked like a charm!  The only thing you will need to add, if you want,
would be a little code to 'copy' a blank 'master' copy of an Excel Workbook
to the location and name of the final .xls file you want.  Pretty simple
stuff, can write that if you want to.

The real beauty of this method, is since it is using ADO, it is MUCH faster
then using automation, and doesn't even require Access OR Excel to be on the
machine you run this from (it just needs MDAC, for ADO).

Drew

_________________________________________________________________
Get holiday tips for festive fun. 
http://special.msn.com/network/happyholidays.armx