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

Senin, 15 Juli 2013

Re: [MS_AccessPros] Exporting data to Excel

Thanks Duane, That is what I was trying to come up with but was having a hard time finding the way to capture the defined query string. Thanks again. All of the MVPs are great teachers and provide one the best services to those of us that are learning.

Thanks again
Rod

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> This is a sample of what I was mentioning http://www.tek-tips.com/faqs.cfm?fid=7433
>
> Duane Hookom MVP
> MS Access
>
> > To: MS_Access_Professionals@yahoogroups.com
> > From: desertscroller@...
> > Date: Mon, 15 Jul 2013 03:40:13 +0000
> > Subject: Re: [MS_AccessPros] Exporting data to Excel
> >
> > 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
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>