Populate from Access table to Excel workbook

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.
        .Sheets("Statements").Select
        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

AarrglesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cquinnCommented:
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:

http://msdn.microsoft.com/en-us/library/aa165427(office.10).aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AarrglesAuthor Commented:
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
SSN
Acct Open Date

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

I would prefer to make ONE table with the fields:
First Name
Last Name
SSN
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
  .Sheets("Applications").Select
  .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!
0
cquinnCommented:
Base a query on the tables, selecting the fields you want, then use the query as the source for copyfromrecordset
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.