• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

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

0
Aarrgles
Asked:
Aarrgles
  • 2
2 Solutions
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now