I was looking for the exact same thing as the person that posted the following question:
1 MS Access Query to Multiple Excel Files Based on Field Value
This answer:
Dim Db As DAO.Database, qdef AS DAO.QueryDef, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT DISTINCT [CustomerName] FROM [QueryName]")
Do While Not rst.EOF
Set qdef = db.QueryDefs("[MyTempQuery]")
qdef.SQL = "SELECT * FROM [QueryName] WHERE Customer = '" & rst!CustomerName & "'"
Set qdef = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MyTempQuery", _
"C:\Path\To\Excel\Files\" & rst!CustomerName & ".xlsx", True
rst.MoveNext
Loop
rst.Close
Set rst = Nothing: Set db = Nothing
...provided by @Parfait worked wonders, but since this access file is going to be used by several other people i now need the access to export the files in a sub folder of the same folder as the Access file instead of specifying an exact folder path in the code. Can anyone help me with that please?
Thank you all in advance!
I found out the solution to this. Basically i declared a variable as a string, and then used that variable to store the location of the access file. Then i created a folder inside that location and used it as the export target.
Dim Db As DAO.Database, qdef AS DAO.QueryDef, rst As DAO.Recordset, expPath As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT DISTINCT [CustomerName] FROM [QueryName]")
expPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
MkDir expPath & "EXPORT"
Do While Not rst.EOF
Set qdef = db.QueryDefs("[MyTempQuery]")
qdef.SQL = "SELECT * FROM [QueryName] WHERE Customer = '" & rst!CustomerName & "'"
Set qdef = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MyTempQuery", _
expPath & "EXPORT\" & rst!CustomerName & ".xlsx", True
rst.MoveNext
Loop
rst.Close
Set rst = Nothing: Set db = Nothing