Export to Excel into separate files

[Elf]

I have a list of Dealers and multiple rows for each Dealer. I need to export all records to Excel and save separate files for each Dealer in the list. I have the code behind a command button on a blank form. It was working but it saved all records into the one excel file. I’m not sure how to filter the query for one Dealer without having to create a report or form first. I just want it to export all records direct from the query. When you see the code below you will know that I am a novice at recordsets (I get mixed up with DAO and ADO) and loops. I'm using Access 2002. I appreciate your help in advance.

Public cnn As ADODB.Connection
Public rst As New ADODB.Recordset

Private Sub Command0_Click()
Dim Path As String
Dim StrDealer As String
Dim intDcode As Long
Dim strQry As String
Dim StrExt As String

'strQry = "qryletterclosure"

Set cnn = CurrentProject.Connection
rst.Open "qryletterclosure", cnn, adOpenKeyset, adLockBatchOptimistic
'Set rst = CurrentDb.OpenRecordset("SELECT * from qryletterclosure ORDER BY qryletterclosure.dealercode;")

Path = "C:\ExcelExport\"
StrDealer = rst("DealerName")
intDcode = rst("DealerCode")
StrExt = ".xls"
'strQry = "SELECT * qryletterclosure ORDER BY qryletterclosure.dealercode WHERE qryletterclosure.dealercode = intDcode;"

Do Until rst.EOF
rst.Filter "dealercode" = intDcode

DoCmd.OutputTo acOutputQuery, rst, acFormatXLS, Path & StrDealer & StrExt, True
rst.MoveNext
Loop
MsgBox ("Export Complete")
End Sub

 

[Dennisk]

the path/name need to in the main loop.

At the moment you are setting it up once prior to the loop

 

[Elf]

Thanks for the suggestion Dennisk but it is still not working. The error I am getting is "Invalid use of property" on the line rst.filter. Any other ideas?

 

[Elf]

Ok. I've changed the code slightly and I am getting 50 excel files saved with different names which is great. However, the records are not filtered for each Dealer. How do I set the filter before the export is done? I've looked at other posts which use docmd.OpenReport or OpenQuery but if I have set the recordset can't I use that??

Set cnn = CurrentProject.Connection
rst.Open "qryletterclosure", cnn, adOpenKeyset, adLockBatchOptimistic
'Set rst = CurrentDb.OpenRecordset("SELECT * from qryletterclosure ORDER BY qryletterclosure.dealercode;")

intDcode = rst("DealerCode")
'strQry = "SELECT * qryletterclosure ORDER BY qryletterclosure.dealercode WHERE qryletterclosure.dealercode = intDcode;"

Do Until rst.EOF
Path = "C:\Erica\ExcelExport\"
StrDealer = rst("Dealer Name")
StrExt = ".xls"
strFile = Path & StrDealer & StrExt
'rst.Filter "dealercode" = intDcode
'rst.Execute "dealercode" = intDcode

DoCmd.OutputTo acOutputQuery, "qryletterclosure", acFormatXLS, strFile, False
rst.MoveNext
Loop
MsgBox ("Export Complete")

 

[jal]

Ok. I've changed the code slightly and I am getting 50 excel files saved with different names which is great. However, the records are not filtered for each Dealer. How do I set the filter before the export is done?

Create a temporary queryDef on the fly as to set the SQL dynamically in code.

Dim qDef as DAO.QueryDef
err.Clear
On Error Resume Next
Set qDef = CurrentDB.QueryDefs("qryTemp")
if err.Number <> 0 Then set qdef = CurrentDB.CreateQueryDef("qryTemp")
On error Goto 0
qDef.SqL = "SELECT ....." <---- put WHERE clause here.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTemp", "C:\Spreadsheet.xls", True
CurrentDb.QueryDefs.Delete "qryTemp"

 

[Elf]

Thanks for the suggestion Jal but I'm still confused with how the SQL filter works. I don't think I can set the 'where' clause in an SQL statement because there are 250 Dealer codes and I can't filter them one by one. My understanding is that it should take the DealerCode from the recordset, pick out the records that match that DealerCode from the qryFundStatus, export those rows to excel and then move to the next DealerCode in the recordset and keep looping. Am I on the right track??? Updated code:

Dim rst As DAO.Recordset
Dim Path As String
Dim StrDealer As String
Dim intDcode As Long
Dim strQry As String
Dim StrExt As String
Dim strFile As String

strQry = "SELECT tblDealers.[DealerCode], tblDealers.[DealerName]FROM tblDealers ORDER BY tblDealers.[DealerName];"
Set rst = CurrentDb().OpenRecordset(strQry, dbOpenDynaset)

rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF

Path = "C:\temp\"
StrDealer = rst("DealerName")
StrExt = " Jan 09.xls"
strFile = Path & StrDealer & StrExt

DoCmd.OutputTo acOutputQuery, "qryfundstatus", acFormatXLS, strFile, False
rst.MoveNext
Loop

MsgBox ("Export Complete")
Set rst = Nothing
End Sub

 

[jal]

Thanks for the suggestion Jal but I'm still confused with how the SQL filter works. I don't think I can set the 'where' clause in an SQL statement because there are 250 Dealer codes and I can't filter them one by one. My understanding is that it should take the DealerCode from the recordset, pick out the records that match that DealerCode from the qryFundStatus, export those rows to excel and then move to the next DealerCode in the recordset and keep looping. Am I on the right track???

Yes, that's what I was expecting you to do. Put my code before the loop. Then within the loop have this code (which is basically your original code)

intDcode = rst("DealerCode")
qDef.SQL = "SELECT * FROM tblDealers WHERE dealercode =" & intDcode;"


And then export to excel within the loop. Look, I'm just trying to get you started - you've got a bunch of queries and tables that I know nothing about it. I'm just giving you the general idea, without trying to comprehend your entire setup.

 

[Elf]

Jal, don't ever doubt that you are a star! My brain is pretty fried by the heat here in Australia but I did what you said and the code works. It gets stuck on some records towards the end but I'm pretty happy with the result. So thank you! Now I'm going to the beach!
Here is the code:


Private Sub Command0_Click()

Dim rst As DAO.Recordset
Dim Path As String
Dim StrDealer As String
Dim intDcode As Long
Dim strQry As String
Dim StrExt As String
Dim strFile As String
Dim qDef As DAO.QueryDef

Err.Clear
On Error Resume Next
Set qDef = CurrentDb.QueryDefs("qryfundstatus")
If Err.Number <> 0 Then Set qDef = CurrentDb.CreateQueryDef("qryfundstatus")
On Error GoTo 0

strQry = "SELECT tblDealers.[DealerCode], tblDealers.[DealerName]FROM tblDealers ORDER BY tblDealers.[DealerName];"
Set rst = CurrentDb().OpenRecordset(strQry, dbOpenDynaset)

rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
Path = "C:\temp\"
StrDealer = rst("DealerName")
StrExt = " Jan 09.xls"
strFile = Path & StrDealer & StrExt
intDcode = rst("DealerCode")
qDef.SQL = "SELECT * FROM tblFundsStatus WHERE dealercode = " & intDcode

DoCmd.OutputTo acOutputQuery, "qryfundstatus", acFormatXLS, strFile, False
rst.MoveNext
Loop

MsgBox ("Export Complete")
Set rst = Nothing
End Sub