Loop Code for Exporting Data to Excel

[R_Whit]

Hi there.

I am currently looking for some code to help me export some data to separate Excel files.

I have a query that is filtered on a Customer Code which I then export to Excel. I have quite a few Customers with which I produce separate reports for. At the moment, this is fairly manual and so would like to automate the process to run using either a macro or module by looking at the query and then filter using a separate table based on the Customer Code.

By looking on the internet I have found a bit of short code which I am sure will work based on a query (as opposed to a Report/Form it was used for).

Below is the code I am trying to use and am stuck on how to edit the existing query and updating with the relevant CstrNo:


Private Sub CstrRun()

Dim MyDB As DAO.Database
Dim strFilter As String
Dim rstCstrNo As DAO.Recordset

Set MyDB = CurrentDb()
Set rstCstrNo = MyDB.OpenRecordset("CstrList", dbOpenForwardOnly)

With rstDlrNo
Do While Not .EOF
DoCmd.OpenQuery "Customer Summary", acViewDesign

strFilter = "[Customer Data].[Cstr No (Num)]" = "!CstrNo"

DoCmd.Close acQuery, "Customer Summary", acSaveYes

DoCmd.OutputTo acOutputQuery, "Customer Summary", acFormatXLS, "C:\Temp\" & ![CstrNo] & "_Customer Summary" & ".xls"
.MoveNext
Loop
End With

End Sub


Any help will be greatly appreciated.

 

[MarkK]

This looks wrong ...

strFilter = "[Customer Data].[Cstr No (Num)]" = "!CstrNo"

I'd expect to see it like this ...

strFilter = "[Customer Data].[Cstr No (Num)] = " & !CstrNo

And naming things:
For clarity, avoid using spaces and obvious terms...

strFilter = "Customer.CstrNo = " & !CstrNo

 

[R_Whit]

Thank you for your code, though it does not seem to 'capture' the CstrCode nor does it seem to resave the query.

If I run through the steps and manualy put in the code, the save works. I think the trick is to make that line of code work some how.

Any other pointers?