Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate Copy and Paste of Table to Excel (Access 97)

    Has anybody got any code kicking about that will (Effectively) copy and paste a table (or query) from and Access database to an Excel workbook?

    I have some code that does this for small recordsets quite well :

    (Should be easy to figure out what the passed variables are)

    Public Function ExportQuerytoExcel(ByRef objexcel, ByRef db, ByRef rstform, placeY As Integer, placeX As Integer)
    Dim Records, RowCol As Long

    With rstform
    .MoveLast

    Debug.Print .RecordCount
    Debug.Print

    Records = rstform.RecordCount
    .MoveFirst
    RowCol = .Fields.Count

    With objexcel
    .Visible = True
    .Sheets(1).Activate

    For Records = 1 To rstform.RecordCount
    For RowCol = 1 To rstform.Fields.Count
    .Cells(placeX + (Records - 1), placeY + (RowCol - 1)).value = rstform.Fields(RowCol - 1)
    '.value could be fields.name?!?!

    If Records = 1 Then .Cells(placeX - 1, placeY + (RowCol - 1)).value = rstform.Fields(RowCol - 1).Name
    Next RowCol
    rstform.MoveNext
    Next Records

    End With

    .close

    End With

    End Function

    But this doesn't handle recordsets that are 100 records plus very well.... Too slow.

    Any thoughts? - Mods, would you prefer I post in another forum? (I thought Access was my best bet rather than VB)
    The solution needs to be available as automation - it's to automatically generate lists in Excel from query results.

    Thanks everybody!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Copy and Paste of Table to Excel (Access 97)

    1. From Access

    The DoCmd object in Access has a TransferSpreadsheet method that can be used to export a table or a saved query to an Excel workbook. Take a look at the Access online help to learn about the details.

    The data will be exported to a worksheet in the specified workbook with the name of the table/query, and start in cell A1. You could create a relatively simple Excel macro to move the exported data to another location.

    2. From Excel

    The Range object in Excel has a CopyFromRecordset methid that can be used to import a DAO recordset. Look up CopyFromRecordset in the Excel VBA online help
    for the details. The recordset can be created from a table, a saved query or an SQL string.

    In your case, you would use something like

    Dim iCols As Integer
    With objExcel
    For iCols = 0 to rstForm.Fields.Count - 1
    .Cells(placeX, placeY + iCols).Value = rstForm.Fields(iCols).Name
    Next
    .Cells(placeX + 1, placeY).CopyFromRecordset(rstForm)
    End With

    Note that the Fields collection is zero-based: the first field has index 0, the last has index Fields.Count - 1

Posting Permissions

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