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

Senin, 15 Juli 2013

Re: [MS_AccessPros] Exporting data to Excel

Duane,
I hope you can help with the code in trying to export to excel. The code sample was very helpful. But I have run into an issue with the Docmd.transferSpreadsheet. Here is the sub that contains everything:

=======
Private Sub Cmd_Export2Excel_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim intCommand As Integer
Dim strOrderBy As String
Dim qdfCopy As QueryDef
Dim rstCopy As Recordset

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("Qry_Common_Flds4Rpts")
Set rst = qdf.OpenRecordset()

If ReportFilter Then
strOrderBy = rptFilter
Set qdfCopy = CopyQueryNew(rst, strOrderBy)
Set rstCopy = qdfCopy.OpenRecordset(dbOpenSnapshot, _
dbForwardOnly)
MsgBox "query = " & qdfCopy.SQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdfCopy.SQL, "C:\b_files\City of Gilbert\Research.xlsx", True
With rstCopy
Do While Not .EOF
MsgBox "Return value = " & !lngTPTID
.MoveNext
Loop
.Close
End With
End If

rst.Close
dbs.Close

End Sub
=======
An error message on the Docmd is as follows:
Run-time error '7871':
The table name you entered doesn't follow Microsoft Access object naming rules.

I am pretty sure it is referring to the query object. The query string looks OK (not terminated with ';'). If the line is commented out the following steps thru the records and displays the proper info.

Any suggestion would be helpful. I am continuing with my troubleshooting. Thanks for your time.
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]
>