export vba not working

[pbuscio]

I know I am missing something obviously, but I had a code that exported to excel and created separate tabs on separate files like I wanted. I used thesame code but changed the particular tables and fields. The new one doesn't work. below is the code, can someone shed some light?

Function runSC_STK_Test()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstVendor As DAO.Recordset
Dim strSQL As String, strTemp As String, strVendor As String, strGroup As String
Dim strDate As Date

' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
' filename without the .xls extension
' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)

Const strFileName As String = "SC_STK_"

Const strQName As String = "zExportQuery9"

strDate = Date

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT qrySC_STK.Group, qrySC_STK.Vendor FROM qrySC_STK;"
Set rstVendor = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstVendor.EOF = False And rstVendor.BOF = False Then
rstVendor.MoveFirst
Do While rstVendor.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strVendor = rstVendor!Vendor.Value
strGroup = rstVendor!Group.Value
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"Planner = " & rstMRP!Planner.Value & ";"
strSQL = "SELECT qrySC_STK.Plant, qrySC_STK.Group, qrySC_STK.Vendor,qrySC_STK.[Vendor Name], qrySC_STK.Material, qrySC_STK.[Material Description]," & _
"qrySC_STK.Qty, qrySC_STK.Value " & _
" FROM qrySC_STK WHERE Vendor = " & strVendor & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "qry_" & strVendor
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strTemp, "V:\WCRP\Supplier_Stk\" & strFileName & strVendor & ".xlsb"
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, "I:\Materials Management\Pat B\" & strFileName & ".xls"&
rstVendor.MoveNext
Loop
End If

rstVendor.Close
Set rstVendor = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

End Function