access table to multiple excel files

[chainedtodesk]

i have a table that can have anywhere from 10 -1000 records in it. these records are broken out by several buckets but have one field in common with another table. examples for testing included.
tble1 - mgrs which has 2 fields mgr#, mgrname. tble2 has mgr# and several other fields i need exported. i want the process to run through tble1 and create seperate excel files for each mgr with the tble2 data in it. need them in seperate excel files so they can be distributed accordingly.

here is code but it will not create any outfiles...

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCT ManagerID FROM ManagersTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\" & strMgr & Format(Now(), _
"MMDDYYYY") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.Close
Set dbs = Nothing

 

[Andrzejek]

When you step thru your code, what does this line do:
[tt]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\" & [red]strMgr[/red] & Format(Now(), _
"MMDDYYYY") & ".xls"
[/tt]
Does it create any xls files? It may just over-write the same file over and over since you do not assign any value to your variable [tt]strMgr[/tt]

Have fun.

---- Andy

 

[chainedtodesk]

yes, i see the error in my ways... looked at a few other posts and have been able to get this to work as needed.

 

[Andrzejek]

What did you do to 'get this to work as needed'?
Some other people visiting here may have similar issue and it would be nice if they would find the answer here.

Have fun.

---- Andy

 

[chainedtodesk]

here is the working code if anyone wants to reference:

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strFileName As String
Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] ;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' code to set strFileName needs

strFileName = "SELECT DISTINCT [ManagerID] FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' code to set strSQL needs

strSQL = "SELECT DISTINCT [ManagerID] FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID

If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' code to set strMgr needs

strMgr = DLookup("[ManagerNameField]", "[ManagersTable]", _
"[ManagerID]='" & rstMgr!ManagerID.Value & "'")

' code to set strSQL needs

strSQL = "SELECT * FROM EmployeesTable WHERE [ManagerID] ='" & rstMgr!ManagerID.Value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\users\public\desktop\" & strTemp & "_" & Format(Now(), _
"MMDDYYYY") & ".xls"
rstMgr.MoveNext
Loop

End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code