Issue with DoCmd.Transferspreadsheet acExport

[Porr via AccessMonster.com]

Hello,

I am attempting to utilize the DoCmd.Transferspeadsheet method to output the
results of my query to a specific spreadsheet. I plan on looping this to go
through a set of criteria and output the results to a tab named "[Criteria]
Dump". Here is my code so far:

Sub NVTop25()
Dim rs As Recordset

'Get Franchise Criteria
Dim rsCriteria As Recordset

Set rsCriteria = CurrentDb.OpenRecordset("SELECT DISTINCT [Franchise]" _
& "from [Franchise_List]")

'Set Criteria

CurrentDb.Execute ("UPDATE [Franchise_Criteria] SET [Franchise_Criteria]= " _
& "'" & rsCriteria![Franchise] & "'")

'Transfer query data to respective Excel sheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"1_NV_All_FINAL", "C:\Documents and Settings\User\Desktop\Test1.xls",
True, rsCriteria![Franchise] & " Dump"

End Sub

As of right now when I run this code Access crashes. My best bet where I am
messing up is when I set the range where I want to query data to be placed.
When I manually type in the range the code works fine. Do I need to define
the range somewhere?

Thanks in advance.

 

[ryguy7272]

That looks pretty good, actually. what is the error message? Can you set
some breakpoints and F8-through the code?
http://www.learnaccessnow.com/chap20d.html

 

[Douglas J. Steele]

The SQL for your recordset seems to be missing a space. Try

Set rsCriteria = CurrentDb.OpenRecordset("SELECT DISTINCT [Franchise] " _
& "from [Franchise_List]")

As well, the Execute method doesn't require parentheses. Try

CurrentDb.Execute "UPDATE [Franchise_Criteria] SET [Franchise_Criteria]= " _
& "'" & rsCriteria![Franchise] & "'"

Also, I'm wondering where the space in the range name is causing problems.
See whether putting quotes around helps

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"1_NV_All_FINAL", "C:\Documents and Settings\User\Desktop\Test1.xls",
True, """" & rsCriteria![Franchise] & " Dump"""

That's four double-quotes in a row before the ampersand, and three
double-quotes in a row at the end.