Transfer query to Excel with query names added to the worksheet tab 1

[scottian]

I have found this bit of code in another thread,

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryYourQueryName1", "PathName.xls", True, "WorkSheetName1"

But i get errors with the 'acSpreadsheetTypeExcel9' argument. is this a reference issue, or am i being thick.

also

What im trying to do is to export a bunch of query results to seperate worksheets in a single Excel file. I would like the code to apply the query name to its respective worksheet tab and name the workbook 'BlahBlah' & date()

however i havent a clue how to apply the query name to the worksheets. Does anyone know how to this?
Thanks for any help given.

"My God! It's full of stars...

 

[JerryKlmns]

These following statements, export 4 queries to the same excel workbook (marked with current date like 20060824) with sheets named after their respective query name. Data_Folder is a full path (\\ServerName\Folder\SubFolder\)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "St1-D", Data_Folder & "St1"& Format(Date, "yyyymmdd") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "St1-A", Data_Folder & "St1"& Format(Date, "yyyymmdd") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "St1-R", Data_Folder & "St1"& Format(Date, "yyyymmdd") & ".xls", True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "St1-T", Data_Folder & "St1"& Format(Date, "yyyymmdd") & ".xls", True

 

[scottian]

do you know why i still get an error with -

'acSpreadsheetTypeExcel9'

variable not defined

im using access97 by the way

"My God! It's full of stars...

 

[JerryKlmns]

scottian

I guess if you have Access 97 you probably have Excel 97 and not Excel 2000 or above. So the appropiate constant should be
acSpreadsheetTypeExcel7 or its value of 5

 

[scottian]

Thanks very much....job done

"My God! It's full of stars...