Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Accessing an Access DB FROM excel

    I would like to open an Ado Record Set from access and inserting into a range in my excel file. I know how to use ADO in access but not excel, can anyone help?

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Accessing an Access DB FROM excel

    Originally posted by mikezcg
    I would like to open an Ado Record Set from access and inserting into a range in my excel file. I know how to use ADO in access but not excel, can anyone help?
    Originally posted by mikezcg
    I would like to open an Ado Record Set from access and inserting into a range in my excel file. I know how to use ADO in access but not excel, can anyone help?
    One way is from Access using code such as:

    Private Function ExportToExcel(rstADORecordset As ADODB.Recordset) As Boolean
    ' Export to Excel and apply a general format to the spreadsheet
    Dim objxl As Excel.Application
    Dim rngExcelRange As Excel.Range
    Dim intCount As Integer

    On Error GoTo ErrorHere
    ' Initialise
    DoCmd.Hourglass HourglassOn:=True
    Set objxl = New Excel.Application
    objxl.ScreenUpdating = False

    ' Export to Excel the recordset
    objxl.Visible = False
    objxl.Workbooks.Add

    ' Copy the recordset to Excel
    ' Get the column headers from the recordset to match the data
    ' Copy and format the field names
    For intCount = 0 To rstADORecordset.Fields.COUNT - 1
    objxl.Cells(1, intCount + 1).Value = rstADORecordset.Fields(intCount).Name
    Next intCount
    ' Define the column headers range
    Set rngExcelRange = objxl.Range(objxl.Cells(1, 1), _
    objxl.Cells(1, rstADORecordset.Fields.COUNT))

    ' Copy the recordset
    objxl.Range("A2").CopyFromRecordset rstADORecordset

    ' Format the column headers
    With rngExcelRange
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    With .Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    End With

    objxl.Range("A2").CurrentRegion.WrapText = False
    MsgBox "Finished Exporting data to Excel", vbInformation, "Finished Export"
    objxl.ScreenUpdating = True
    objxl.Visible = True
    ExportToExcel = True
    ExitHere:
    DoCmd.Hourglass HourglassOn:=False
    Set rstADORecordset = Nothing
    Set objxl = Nothing
    Set rngExcelRange = Nothing
    Exit Function
    ErrorHere:
    MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
    & "Procedure: ExportToExcel" & _
    vbCrLf & "Error Code: " & Err.Number & _
    vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
    ExportToExcel = False
    Resume ExitHere
    End Function

    You will need to add a reference to Excel - In the VBA editor goto Tools, References and find Microsoft Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •