command to export query to excel 2003 worksheet

[rsbutterfly16]

hi guys, i need your help, i have a form call "test" wiht a query called
"invoice" (this query is linked from access to sql) in which i have to export the results into an excel worksheet, i
have a vba code from the cmd button to perform this but i keep gettting an
error message: runtime error '3251 operation not supported for this type of
object, can someone help ? my access database in in access 2000

Private Sub cmdExcel_Click()


Dim strQryName As String, strXLFile As String, strName As String
strQryName = "Invoice query"
strXLFile = "C:\Documents and Settings\strName\Desktop\Invoice .xls"
strName = GetUserName()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strQryName, strXLFile
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink "C:\Documents and Settings\strName\Desktop\Invoice.xls"

End If

End Sub

 

[ideefixe]

You may need to set the SQL Security Check registry key if you are working in XP, since that resists automation from Access to Word or Excel:

http://support.microsoft.com/kb/825765/en-us

Presumably if you execute menu option to "analyse with Microsoft Excel" on the same query, it runs ok?

 

[john471]

This construct looks wrong, but it may not be the cause of your immediate problem.

strXLFile = "C:\Documents and Settings\strName\Desktop\Invoice .xls"
strName = GetUserName()

and later

FollowHyperlink "C:\Documents and Settings\strName\Desktop\Invoice.xls"

I suspect you intended...

strName = GetUserName()
strXLFile = "C:\Documents and Settings\" & strName & "\Desktop\Invoice.xls"  'note: also removed space before (.xls)

and later

FollowHyperlink strXLFile

HTH

Rerards

John