ACCESS FEED: RE: [MS_AccessPros] Exporting data to Excel

Minggu, 14 Juli 2013

RE: [MS_AccessPros] Exporting data to Excel

Rod,

I think you are working too hard. There shouldn't be any need to open a recordset in order to export the query to Excel.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_Common_Flds4Rpts", "C:\b_files\City of Gilbert\Research.xlsx", True

I expect the issue is probably caused by references to controls from forms in the query criteria. I typically use a little DAO code to convert the control references to the values.

Duane Hookom MVP
MS Access

----------------------------------------
> From: desertscroller@cox.net
>
> Hi All,
> I am trying to export some data to Excel 2010 from Access 2010. I am getting a error about one of the parameters for DoCmd.TransferSpreadsheet
>
> Not sure if everything is quite right yet. The code I am using is as follows:
> ========================================
> Private Sub Cmd_Export2Excel_Click()
> Dim dbs As Database, rst As DAO.Recordset, qdf As DAO.QueryDef
> Dim rstFiltered As DAO.Recordset
> Dim rptFilter As Variant
> Dim qStr As String
>
> Set dbs = CurrentDb()
>
> 'Get the parameter query
> Set qdf = dbs.QueryDefs("Qry_Common_Flds4Rpts")
> Set rst = qdf.OpenRecordset()
>
> If ReportFilter Then
> rst.Filter = rptFilter
> Set rstFiltered = rst.OpenRecordset
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rstFiltered, "C:\b_files\City of Gilbert\Research.xlsx", True
> Else
> End If
> dbs.Close
>
> End Sub
> ==============================================
> ReportFilter is a function used to build the filter based on inputs from the user. The filter looks good and very thing works when used in presenting a report in Access. The user wants to be able to export the data to excel provide his supervisor.
>
> One of my basic questions is whether I can export the filtered recordset or if I will be to process each record individually. Secondary is if I need to setup an object for excel with the code to process to.
>
> Thanks for any help.
> Rod