DoCmd - Run-time Error 3011

[HappyLadyToo]

Hi Again,

I've been working on a function with a DoCmd and can't quite get it to work. I've been researching and making changes according to what I've been reading, but I'm at a loss.

The objective is to have an Access query run and export the data into separate Excel workbooks. If exporting from the Access table is better, I'm all for suggestions.

Field to filter: Vendor_Name
Query name: FBKO Supplier (a1_FBKO_to_Supplier)
Table name: a1_FBKO_to_Supplier





Option Compare Database
Option Explicit


Function This()




Dim tmpRS As DAO.Recordset, strSQL As String
Set tmpRS = CurrentDb.OpenRecordset("Select Vendor_Name from [FBKO Supplier (a1_FBKO_to_Supplier)] group by Vendor_Name")
Do While Not tmpRS.EOF
strSQL = "SELECT [FBKO Supplier (a1_FBKO_to_Supplier)].* FROM [FBKO Supplier (a1_FBKO_to_Supplier)] WHERE Vendor_Name = '" & tmpRS.Fields(0) & "'"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
strSQL, "C:\Users\u138044\Documents\test files\ - " & tmpRS.Fields(0) & ".xlsx", True
tmpRS.MoveNext
Loop
Set tmpRS = Nothing


End Function

[jonh]

Which part are you at a loss with?

Does the SQL run ok from a query?
Not sure why you are adding " - " to the beggining of the filename but other than that nothing really stands out as obviously wrong.

Fyi, it's better if you only use normal characters and numbers in object names. No spaces or brackets, etc.

[HappyLadyToo]

Jonh, You are correct in that I don't need the "-" before the file name however I still can't get it to work. It's not a problem though because I found a better way to do what I need to do. Thanks for the input.