Populate from Access table to Excel workbook

Posted on 2008-11-06
Last Modified: 2013-11-27
Another company we interact with only accepts their media requests through a heavily formatted Excel workbook.  I've already written code to open the Excel file that's saved as the template and write values to it.

What I don't know how to do is to tell it write values from a table in Access to a range of cells because the data in the table fluctuates (from 1 to 20x request) and the workbook they have provided has columns to the left and right of said range that do not apply to the data.

I've attached my code.  The Access table I want to get the data from is called ABCCompany.   The sheet I need to write to is called "Statements" and the columns that I need to write between are C and I.   If necessary, you can make up the names of the fields/columns as I just need an example.

Thank you in advance for your assistance!
Option Compare Database

Option Explicit

Function PopulateABC()

Dim ExlApp As Object

Dim strFilename As String

Dim TodaysDate As String

Dim YourName As String

Dim YourEmailAddress As String

Dim SaveAsPath As String

'Defines the strings.

strFilename = "C:\ABCMediaRequest.xls"

TodaysDate = Format(Date, "mm/dd/yy")

YourName = Forms![ABC}![YourName]

YourEmailAddress = Forms![ABC]![YourEmailAddress]

SaveAsPath = "c:\Test123.xls"

'Opens Microsoft Excel.

Set ExlApp = CreateObject("Excel.Application")

'Opens the Master Excel workbook and makes it visible.

With ExlApp

        .Visible = True

        .Workbooks.Open (strFilename)

End With

With ExlApp

'Populates the Order Summary Sheet.

        .Sheets("Order Summary").Select

        ActiveSheet.Cells(6, "B").Value = TodaysDate

        ActiveSheet.Cells(7, "B").Value = YourName

        ActiveSheet.Cells(8, "B").Value = YourEmailAddress

 'Populates the Statements Sheet.


        ActiveSheet.Range("???").Value = Array("???")

End With

'Saves the Workbook as another file.

With ExlApp

        ActiveWorkbook.SaveAs (SaveAsPath)

End With

'Clean up.

Set ExlApp = Nothing

End Function

Open in new window

Question by:Aarrgles
    LVL 15

    Accepted Solution

    You can use the CopyFromRecordset function to populate the spreadsheet:

    Iy you have a recordset called rstExcel containing the data to be copied, and have ensured this matches the columns needed to populate in the spreadsheet - in this example, row 1 in the spreadsheet already contains the column headers, so I copy the data starting at Row 2, Column 1

      ActiveSheet.Cells(2, 1).CopyFromRecordset rstExcel

    More details can be found here:

    Author Comment

    Ah thank you so much!   It works perfectly for what I asked for.
    I do want to amend this question though...say the Applications and Statements sheets are almost identical in information needed on the Excel sheet.  

    For instance Applications has the fields:
    First Name
    Last Name
    Acct Open Date

    And Statements has the fields:
    First Name
    Last Name
    Statements Beginning

    I would prefer to make ONE table with the fields:
    First Name
    Last Name
    Open Date
    Statements Beginning

    Now I know that I can specify the maximum number of columns I want to copy over from the recordset so for Applications I can say:

    With ExlApp
      .ActiveSheet.Cells(5, "C").CopyFromRecordset rstExcel, ,4
    End With

    So it copies the first 4 fields of the recordset beginning in cell C5.  

    If I want to essentially do the same thing for Statements but include StatementsBeginning field and NOT Open Date, how do I select specific fields from the recordset?  Would I just do an SQLSelect Query at that point?  If it is really a hassle then I don't mind just making a separate table, but I'd prefer not to.

    Thanks so much!
    LVL 15

    Assisted Solution

    Base a query on the tables, selecting the fields you want, then use the query as the source for copyfromrecordset

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now