access command in form to export query results to excel

[rsbutterfly219 via AccessMonster.com]

hi guys, i need your help, i have a form call "test" wiht a query called
"invoice" 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 pleaseL!!!! my access database in in access 2000




Private Sub cmdExcel_Click()

Dim xlExc As Object, xlWB As Object
Dim strQryName As String, strXLFile As String
strQryName = "Invoice"
strXLFile = "C:\Documents and Settings\test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strQryName,
strXLFile
Set xlExc = CreateObject("Excel.Application")
xlExc.Visible = True
Set xlWB = xlExc.Workbooks.Open(strXLFile)
Set xlWB = Nothing
Set xlExc = Nothing
End Sub

** the debug tool points at the line starting wiht docmd.transferspreadsheet..
...

any help would be really apreciate it!!!

 

[fredg]

hi guys, i need your help, i have a form call "test" wiht a query called
"invoice" 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 pleaseL!!!! my access database in in access 2000

Private Sub cmdExcel_Click()

Dim strQryName As String, strXLFile As String
strQryName = "Invoice"
strXLFile = "C:\Documents and Settings\test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strQryName,
strXLFile
Set xlExc = CreateObject("Excel.Application")
xlExc.Visible = True
Set xlWB = xlExc.Workbooks.Open(strXLFile)
Set xlWB = Nothing
Set xlExc = Nothing
End Sub

** the debug tool points at the line starting wiht docmd.transferspreadsheet..

You don't all of that stuff.

Private Sub cmdExcel_Click()

Dim strQryName As String, strXLFile As String
strQryName = "Invoice"
strXLFile = "C:\Documents and Settings\test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
strQryName, strXLFile

End Sub

However, I don't believe "C:\Documents and Settings\test.xls", by
itself, is a valid path. I think, because it's in Documents and
Settings, it should have more folders, i.e.
"C:\Documents and Settings\Owner\Desktop\test.xls"

 

[rsbutterfly219 via AccessMonster.com]

thanks so much but i keep getting the error message

runtime error '3251 operation not supported for this type of object.

is there a reference library i should make sure to have.??

hi guys, i need your help, i have a form call "test" wiht a query called
"invoice" in which i have to export the results into an excel worksheet, i

[quoted text clipped - 3 lines]

Private Sub cmdExcel_Click()

Dim strQryName As String, strXLFile As String
strQryName = "Invoice"

[quoted text clipped - 9 lines]

** the debug tool points at the line starting wiht docmd.transferspreadsheet..

You don't all of that stuff.

Private Sub cmdExcel_Click()

Dim strQryName As String, strXLFile As String
strQryName = "Invoice"
strXLFile = "C:\Documents and Settings\test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
strQryName, strXLFile

End Sub

However, I don't believe "C:\Documents and Settings\test.xls", by
itself, is a valid path. I think, because it's in Documents and
Settings, it should have more folders, i.e.
"C:\Documents and Settings\Owner\Desktop\test.xls"