Duane,
Thanks for the input. As part of the data, I am trying to filter the query slightly based on inputs from the user. (This query is used as part of a report where I can filter it.) Trying to use the query in multiple environments.
If possible could you point me to a small example to using the DAO code. I think I know what you are telling me but I would like to make sure I fully understand.
Rod
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> 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@...
> >
> > 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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar