0

I am working on an export code to copy and export the current record I am viewing to an excel spreadsheet. Below is my code

Private Sub cmdExportFilename_Click()
    Dim sFilename As String
    Dim StrSQL As String
    StrSQL = "Select * FROM [MainData] WHERE ([ID]=" & Me![ID] & ");"
    sFilename = "C:\Desktop\exportResults.xlsx"
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrSQL, 
 sFilename, True

 End Sub

When I run this I get a Run-time error '3011' saying it could not find the object (though it pulls my Select string and correctly identifies I'm viewing record 86 with the error message" and says the object doesn't exist.

My previous code successfully exported data but it outputted the entire query results instead of just the viewed record.

Private Sub cmdExportFilename_Click()
Dim sFilename As String
sFilename = "C:\Users\cpastore\Desktop\exportResults.xlsx"
DoCmd.OutputTo acOutputForm, "OpenComplaintsQuery", acFormatXLSX, sFilename, 
AutoStart:=True

End Sub

With Outputto command I do not see where I can select certain things in Parameters. So I thought I would try TransferSpreadsheet command instead.

My end goal ultimately is with the record I am viewing, when I click the button, it exports 6 or 7 fields of 25 that the form displays to an excel spreadsheet where those values Goto a very specific cell location in the sheet. I know with codes above I am a long way from there but I am slowly learning.

Edit: Adding new Code per June7 post suggestion. Ran into another Runtime Error

Private Sub cmdExportfield_Click()
Dim rsGroup As DAO.Recordset
Dim QIMS As String
Dim path As String

path = "C:\Desktop\"

    Set rsGroup = CurrentDb.OpenRecordset("SELECT 
OpenComplaintsQuery.QIMS# " & "FROM OpenComplaintsQuery GROUP 
BY OpenComplaintsQuery.QIMS#", dbOpenDynaset)

Do While Not rsGroup.EOF
 QIMS = rsGroup!QIMS#

 Dim rsExportSQL As String

 rsExportSQL = "Select * FROM OpenComplaintsQuery" & "WHERE 
(((OpenComplaintsQuery.QIMS#='" & QIMS & "'))"

 Dim rsExport As DAO.QueryDef
 Set rsExport = CurrentDb.CreateQueryDef("myexportquerydef", 
rsExportSQL)

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, 
"myexportquerydef", path & "exporttest.xlsx", True

 CurrentDb.QueryDefs.Delete rsExport.Name

 rsGroup.MoveNext

Loop

End Sub
Kemidan2014
  • 17
  • 6
  • An SQL statement in the table name argument will not work. It is looking for a table or query with that SQL statement as name. If you want to export a single record then apply filter criteria to object you want to export. – June7 Feb 14 '23 at 18:29
  • Does this answer your question? [Exporting Recordset to Spreadsheet](https://stackoverflow.com/questions/16336025/exporting-recordset-to-spreadsheet) – June7 Feb 14 '23 at 18:33
  • June, I reviewed the post you are linked. I attempted a version of this just targeting only 1 field and I get an Runtime error 3075. I am sure it is because I do not know how to fully translate what needs to be replaced to match my field names / query names. – Kemidan2014 Feb 14 '23 at 22:05
  • Edit question to show your attempted code. Example shows code to modify query object and run TransferSpreadsheet. Alternative is apply filter to form or report and use OutputTo method. – June7 Feb 14 '23 at 22:40
  • Another approach for TransferSpreadsheet is for query object to have parameter that references control on form for input. Then don't need the VBA to modify query object. – June7 Feb 14 '23 at 22:46
  • I have editted the post to put the new code at the bottom. regarding your other approach can you share an example? – Kemidan2014 Feb 15 '23 at 14:00
  • Runtime error on what line? Do you know how to debug code? Looks like you are not constructing line continuation correctly. I expect that is cause of error. https://stackoverflow.com/questions/19422064/break-a-long-sql-vba-statement-into-multiple-lines – June7 Feb 15 '23 at 20:09
  • Query object SQL like: `SELECT * FROM table WHERE ID=Forms!formname!textboxname` – June7 Feb 15 '23 at 20:11
  • You are using special characters in naming convention (QIMS#). This means have to enclose in `[]`: `[QIMS#]`. Advise not to use spaces nor punctuation/special characters (underscore is only exception) in naming convention. Better would be QIMSNum or QIMS_Num. – June7 Feb 15 '23 at 20:16
  • Ah ha Now i see the syntax, Thank you June7! – Kemidan2014 Feb 15 '23 at 20:23

0 Answers0