cmd to export query results from a form to excel spreadsheet
- 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!!!
-
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
-
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.
-
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,
-
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....
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply