I want to run some code which will move through a table to change variables.

My table looks a little like this:

Account Location Name

1001 C:\Folder1\John.xls John
1002 C:\Folder1\Jame.xls James
1003 C:\Folder1\Jill.xls Jill

And i want to run the below code so that the account value from the table is used as a filter on Query1 and the Location value from the table is used as the export location.

DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcel(*.xls)", "Location value from Table", False, ""

You have to open the table and loop thru each record and modify the Query1's SQL on fly.

Create code looks sthg like this.


Private Sub Export2Xcel()
Dim dbs As Database, qdf As QueryDef, rst As Recordset
Dim I As Integer, strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Query1")
Set rst = dbs.OpenRecordset("YourTableNameHere")
If Not rst.EOF Then
For I = 1 To rst.RecordCount
' Copy the Query1's SQL and paste it here and add Where clause sthg like this.
strSQL = "Select blah, blah, blah, From ... Where Account = '" & rst("Account") & "'"
qdf.SQL = strSQL
DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcel(*.xls)", rst("Loaction"), False, ""
rst.MoveNext
Next I
End If
rst.Close
qdf.Close
dbs.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
End Sub

Tim,

This worked perfectly, BUT i need to do the same with a table now.

So i want the account value in First Table to act as a filter on the second table.

After each filter is supplied in the loop i will export the table to excel as in the previous code.

I tried to adapt the code myself but failed.

Thanks for your help.