cmd to export query results from a form to excel spreadsheet

Question

cmd to export query results from a form to excel spreadsheet

jessica-321853

SSCrazy

Points: 2207
More actions
June 5, 2006 at 12:36 pm

#169149

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 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!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Unless you are running as Local Administrator, you do not have permission to write to the C:\Documents and Settings folder, try changing this to a folder that you can write to, like C:\Documents and Settings\<user name>\My Documents.

Andy

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

The sad fact is that Access does not support exporting directly from a sql pass-thru query into Excel. Not even in version 2003. You need to create another Access query to serve as a "wrapper" for the pass-thru, then it will work.

Steve Medvid SSC Rookie Points: 25 More actions · Answer 3

I see a couple of options off top of my head:

- Loop through a recordset of the data and write line items to the Excel Application New Workbook Object.

- Use SQL Server DTS Package...

htwh,

jessica-321853 SSCrazy Points: 2207 More actions · Answer 4

thanks for your advise, i want to create a cmd button the same thing as: tools, analyze with excel . but I don't seem to get it to work with that vba code....