How to export data from a query in MS Access into a specific cell in an Excel spreadsheet

Could anyone recommend a good link I could read to understand how to export data from a query into a specific cell in an Excel spreadsheet?
LillyCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Lilly,

"export from a query", implies many values

yet " into a specific cell" is just one place.

are you wanting several possible rows of recordset to be transferred? and a row with labels?

by "cell", do you mean upper-left for a range?

here is basic code to copy the results of a query to a new workbook.
Sub CopyFromRecordset_query( _
   psPath As String _
   , psQueryOrTableName As String)
 
   On Error GoTo Proc_Err
 
   ' originally written by Nate Oliver
   ' modified by crystal (strive4peace)
 
   ' NEEDS REFERENCE
   '   Microsoft ActiveX Data Objects #.# Library

  'Declare your ADO Recordset
  Dim rs As ADODB.Recordset
 
  'Excel Objects
   'early binding for development
'   Dim xlApp As Excel.Application _
      , xlWb As Excel.Workbook
   
   'late binding for deployment
   Dim xlApp As Object _
      , xlWb As Object
 
  'Field Names - Stack into Array
  Dim asFieldNames() As String
 
   'loop counters
  Dim j As Long _
   , i As Long
 
  Dim sPathFileExcel As String
  sPathFileExcel = psPath & psQueryOrTableName
 
  i = 1
 
  'OLE - Create xl Objects
   Set xlApp = CreateObject("Excel.Application")
 
  'Add a new Workbook, with one Worksheet, to our Excel Application
  Set xlWb = xlApp.Workbooks.Add(1)
 
  'this is commented out because to show you can loop if you want
  'For i = LBound(sqlArr) To UBound(sqlArr)
 
    'New ADO Recordset
    Set rs = New ADODB.Recordset
 
    'Open the Recordset, Passing the Sql from our Array
    rs.Open CurrentDb.QueryDefs(psQueryOrTableName).SQL _
      , CodeProject.Connection _
      , adOpenStatic _
      , adLockReadOnly
 
    With rs
      'Stack a String Array with the Field Names
      ReDim asFieldNames(0 To .Fields.Count - 1)
 
      For j = LBound(asFieldNames) To UBound(asFieldNames)
        asFieldNames(j) = .Fields(j).Name
      Next j
 
      'Pass Data to Excel!
      With xlWb.Worksheets

         'Add a Worksheet if we're at 2nd Recordset or Greater -- when looping
        'If i > 1 Then .Add After:=.Item(i - 1)

         'Refer to the Worksheet by Item Number
         'in the Collection of Worksheets (1-based)

        With .Item(i)

           'Pass dynamic Field String Array to A1,
           'stretched to the Right for number of Elements
           Let .Range("a1").Resize(, UBound(asFieldNames) + 1).Value = asFieldNames
 
           'Copy our Current Recordset to A2
          .Range("a2").CopyFromRecordset rs
 
          'Rename Worksheet
          .Name = "WorksheetName"
 
          'best-fit columns of data
          .Columns("A:G").EntireColumn.AutoFit
 
        End With
      End With
    End With
 
   'Moving on, no need to close or terminate our RS, yet,
   ' we're going to recycle in the Loop
 
 'end of optional loop
 'Next
 
 'Make Excel visible - (Otherwise Save and Close)
 With xlApp
    .Goto xlWb.Worksheets(1).Range("A1")
    'commented but can be activated if desired
    'xlSht.Cells(1, 1).Activate
    '.Visible = True
 End With
 
Save_Workbook:
 
   'delete file if it already exists
   If Dir(sPathFileExcel) <> "" Then
     On Error Resume Next
     Kill sPathFileExcel
     DoEvents
     On Error GoTo Proc_Err
   End If
   
   'commented because there was not code to disable in this case
   'if you are writing to a template with code, you may want
   'to disable events in the beginning
   'xlApp.EnableEvents True
 
   'save workbook
   xlApp.ActiveWorkbook.SaveAs sPathFileExcel
   'close without saving
   xlApp.ActiveWorkbook.Close False

   'comment message if desired
   MsgBox "Done exporting " & psQueryOrTableName & " to Excel" _
      & vbCrLf & vbCrLf & "File name is " & sPathFileExcel _
      , , "Done"
      
Proc_Exit:
  On Error Resume Next
 
  'Terminate our Excel Object Variables
  Set xlWb = Nothing
  If TypeName(xlApp) <> "Nothing" Then
   xlApp.Quit
   Set xlApp = Nothing
  End If
 
   'Now close and terminate the ADO Recordset, we're all done!!
  rs.Close: Set rs = Nothing

  Exit Sub
 
Proc_Err:
  MsgBox Err.Description, , "ERROR " & Err.Number & "  CopyFromRecordset_query"
  Resume Proc_Exit
  'press Ctrl-Break to stop code at Msgbox
  'set this to be the next statement then F8 to step through code and debug
  Resume

End Sub

Open in new window


if this is not what you are looking for, could you describe more about what you want? If you have questions about how this works, we are happy to answer your questions.

For more information on the error handling, here is a short video:

https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html
(there are 2 other videos in this series if you wish to know more )

If you just have one value that you want to write to a cell, code can be modified to make this happen -- not quite sure what you are after ... besides understanding, which is great! There are comments in the code, but if there is anything you don't understand, ask ~

DAO could be used instead of ADO since this is being run from Access.  If code is run from Excel, in my experience, using ADO is often better. Both DAO and ADO have CopyFromRecordset

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LillyCAuthor Commented:
Great, thanks very much Crystal.  I'll have a go with the code and come back to you if I have any questions :)
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Lilly! ~ happy to help

~crystal
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Lilly, in case you are interested, here is another example of using CopyFromRecordset  to write the results of an Access query to Excel. Rather than parameters for query name and path\file, which you should still send, this also has VBA to format, best-fit columns, and set the margins, orientation, centering, title rows, title columns, and right header for printing

http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.