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

Export Form Datasheet Results To Excel Spreadsheet

P: 13
I have a form that searches dynamically based on what criteria the user enters. The resulting query populates a subform in datasheet view.

If I wanted to have a button that would export the results of the query that is the source for that datasheet form to excel. How would I go about doing that through VBA?

Thanks in advance.
Jul 21 '07 #1
Share this Question
Share on Google+
1 Reply


P: 13
I figured it out.

Expand|Select|Wrap|Line Numbers
  1. Dim oExcel As Object
  2.    Dim oBook As Object
  3.    Dim oSheet As Object
  4.  
  5.    'Start a new workbook in Excel
  6.    Set oExcel = CreateObject("Excel.Application")
  7.    Set oBook = oExcel.Workbooks.Add
  8.  
  9.    'Add data to cells of the first worksheet in the new workbook
  10.    Set oSheet = oBook.Worksheets(1)
  11.    oSheet.Range("A1").Value = "Doc #"
  12.    oSheet.Range("B1").Value = "Document Name"
  13.    oSheet.Range("C1").Value = "Revision"
  14.    oSheet.Range("D1").Value = "Status"
  15.    oSheet.Range("E1").Value = "Notes"
  16.    oSheet.Range("A1:E1").Font.Bold = True
  17.  
  18.    Dim rs As Recordset
  19.    Set rs = Me.subResults.Form.Recordset
  20.  
  21.    Dim iRow As Integer
  22.    iRow = 2
  23.    Dim iRows As Integer
  24.    iRows = rs.RecordCount
  25.  
  26.    Do While iRow <= iRows
  27.  
  28.     oSheet.Range("A" & iRow).Value = rs.Fields(0)
  29.     oSheet.Range("B" & iRow).Value = rs.Fields(1)
  30.     oSheet.Range("C" & iRow).Value = rs.Fields(2)
  31.     oSheet.Range("D" & iRow).Value = rs.Fields(3)
  32.     oSheet.Range("E" & iRow).Value = rs.Fields(4)
  33.  
  34.     iRow = iRow + 1
  35.     rs.MoveNext
  36.    Loop
  37.  
  38.    rs.MoveFirst
  39.  
  40.    'Save the Workbook and Quit Excel
  41.    Dim sSave As String
  42.    Dim sTime As String
  43.    sTime = Replace(Replace(Now(), ":", "-"), "/", "-")
  44.    sSave = "QMD Search Export " & sTime & ".xls"
  45.    oBook.SaveAs "Y:\" & sSave
  46.    oExcel.Quit
  47.    Dim result
  48.    result = MsgBox("Excel Spreadsheet saved under your 'Y:\' as '" & sSave & "'", _
  49.             vbOKOnly, "Export Successful")
  50.  
Jul 22 '07 #2

Post your reply

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