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

Minggu, 14 Juli 2013

RE: [MS_AccessPros] Exporting data to Excel

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@cox.net
> 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]