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
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.
.Visible = True
'Populates the Order Summary Sheet.
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("???")
'Saves the Workbook as another file.
Set ExlApp = Nothing