By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

How to export query result to EXCEL file from VBA

P: 5
Hi,

I have to export query result to EXCEL file in certain location.

how do I do that?

I have used

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, , , , , acExportQualityScreen

And getting below error

runtime error: 3066
Query must have already one destination field.

Please help.
Nov 12 '08 #1
Share this Question
Share on Google+
2 Replies


P: 68
Hi

Assuming you've passed the query name to the variable stDocName and so on, and the query has fields in it, I can't see anything obviously wrong with what you're doing, but here's some code I've used to export tables, from a list within a listbox [lstTableNames], which definitely works. Note that this also gives the user the option to export to another access db, by selecting from a combo [cboFormat], so you'll need to edit rather than use straight from the page.

HTH
Kevin


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExport_Click()
  2. On Error GoTo Proc_Err
  3.  
  4.     Dim frm As Form, ctl As Control
  5.     Dim varItm As Variant
  6.     Dim MyTable As String
  7.     Dim MyFileName As String
  8.  
  9.     Set frm = Forms!frmExport
  10.     Set ctl = frm!lstTableNames
  11.  
  12.     For Each varItm In ctl.ItemsSelected
  13.         MyTable = ctl.ItemData(varItm)
  14.         Select Case Me.cboFormat
  15.         Case 1 ' export to an access db
  16.             Application.Echo False
  17.             On Error GoTo AccessError
  18.             MyFileName = Me.txtFileName
  19.             DoCmd.TransferDatabase acExport, "Microsoft Access", MyFileName, acTable, MyTable, MyTable
  20. AccessError:
  21.             Select Case Err.Number
  22.             Case 94
  23.             MsgBox "To export into MS Access you have to select a pre-existing Access database file. " & _
  24.             "Select a valid destination and click Export again.", vbInformation + vbOKOnly + vbDefaultButton1, "Case Management Database"
  25.             Case 2025
  26.             MsgBox "The selected destination file is not an Access database. " & _
  27.             "Please select a valid destination and click Export again.", vbInformation + vbOKOnly + vbDefaultButton1, "Case Management Database"
  28.             Case 3024
  29.             MsgBox "The selected destination file does not exist. " & _
  30.             "To export into MS Access you have to select a pre-existing Access database file. " & _
  31.             "Please select a valid destination and click Export again.", vbInformation + vbOKOnly + vbDefaultButton1, "Case Management Database"
  32.             GoTo Proc_Exit
  33.             End Select
  34.         Case 2 ' export to excel file(s)
  35.             DoCmd.OutputTo acOutputTable, MyTable, acFormatXLS
  36.         End Select
  37.     Next varItm
  38.  
  39. Proc_Exit:
  40.     Application.Echo True
  41.     Set frm = Nothing
  42.     Set ctl = Nothing
  43. Exit Sub
  44.  
  45. Proc_Err:
  46.     'ErrorTrap
  47.     Resume Proc_Exit
  48.  
  49. End Sub
Nov 12 '08 #2

P: 5
I have solved this problem.
I had problem in my query.


Thank you.
Nov 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.