Export query results to Excel into multiple tabs

[pi6]

I need to export query results to Excel file into multiple tabs.
1. The query is:

TRANSFORM Sum(tbHeldClaims.Amount) AS SumOfAmount
SELECT tbHeldClaims.Facility, tbHeldClaims.LOB, tbHeldClaims.Code, tbHeldClaims.Description
FROM tbHeldClaims
WHERE (((tbHeldClaims.Facility)=GetPublicFacility()))
GROUP BY tbHeldClaims.Facility, tbHeldClaims.LOB, tbHeldClaims.Code, tbHeldClaims.Description
PIVOT tbHeldClaims.RunDate;

2. I created a module:

Option Compare Database
Option Explicit

Public PubFacility As String
Public Function GetPublicFacility() As String
GetPublicFacility = PubFacility
End Function

3. VBA script:

Private Sub cmdHelds_Click()
Dim Facility As String
Dim db As Database
Set db = CurrentDb()
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT Facility FROM tbHeldClaims group by Facility;")

Do While Not rs.EOF

PubFacility = rs!Facility
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "cqHeldAmntByWk", "T:\Admin\PACCT\CBO Billing Report" & "WeeklyHeldClaims" & ".xls", True, PubFacility

DoEvents

rs.MoveNext
Loop
Facility = vbNullString
rs.Close
Set rs = Nothing
MsgBox "Finished!"
End Sub

It is looping and finds results by facility, but doesn't create Excel file.
Any help is greatly appreciated.

 

[NickHa]

What name do you expect for the output file? I ask because the constructed name is "T:\Admin\PACCT\CBO Billing ReportWeeklyHeldClaims.xls" - which looks as if it may be missing a "\".
Does the file get created if you use a local drive (e.g. C:\)?

 

[pi6]

THANK YOU!
I added a "\", and it worked as a magic.