VBA/Excel/Access/Word/Access/Access to Excel

Copying Records to an Excel Spreadsheet

   <source lang="vb">

Option Compare Database Option Explicit " be sure to select Microsoft Excel Object Library in the References dialog box Public myExcel As Excel.Application Sub CopyToExcel()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim wbk As Excel.Workbook
  Dim myWorksheet As Excel.Worksheet
  Dim StartRange As Excel.Range
  Dim strConn As String
  Dim i As Integer
  Dim f As Variant
  On Error GoTo ErrorHandler
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
  Set conn = New ADODB.Connection
  Set myRecordset = New ADODB.Recordset
  With myRecordset
     .Open "Employees", strConn, _
         adOpenKeyset, adLockOptimistic
  End With
  Set myExcel = New Excel.Application
  Set wbk = myExcel.Workbooks.Add
  Set myWorksheet = wbk.ActiveSheet
  myExcel.Visible = True
  i = 1
  With myRecordset
     For Each f In .Fields
        With myWorksheet
           .Cells(1, i).Value = f.Name
           i = i + 1
        End With
     Next
  End With
  Set StartRange = myWorksheet.Cells(2, 1)
  StartRange.CopyFromrecordset myRecordset
  myRecordset.Close
  Set myRecordset = Nothing
  myWorksheet.Columns.AutoFit
  wbk.Close SaveChanges:=True, _
      FileName:="C:\ExcelFile.xls"
  myExcel.Quit
  Set conn = Nothing
  Exit Sub

ErrorHandler:

  MsgBox Err.Description, vbCritical, _
     "Automation Error"
  Set myExcel = Nothing
  Exit Sub

End Sub

</source>
   
  

Exporting to a Spreadsheet

   <source lang="vb">

Sub transSpread()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
          "tblSales", "C:\Sales.xls"
    MsgBox "Sales spreadsheet created"

End Sub

</source>