Rod,
You should be explicit with your Dim statements:
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
We don't know what ReportFilter, rptFilter, CopyQueryNew are.
Why are you opening a recordset? What's the need for displaying the !lngTPTID?
You would not use the qdfCopy.SQL. The argument should be a query or table name.
Duane Hookom MVP
MS Access
> From: desertscroller@cox.net
> Date: Tue, 16 Jul 2013 02:27:34 +0000
> Subject: 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]
>>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar