My form fields will populate an Excel workbook. No problems there. My question is how do I call it to enter the data into an Excel template I have already created?

The location of the template sits in my C drive- C:\DailyLogs\DailyLog.xlsx

Here's my code so far:

#         

' Here we go. All the data in the form to an Excel sheet. Cross your fingers...
#         Dim oExcel As Object
#         Dim oBook As Object
#         Dim oSheet As Object
#  
#         'Start a new workbook in Excel- this isn't really what I need though, I need it to open a template and populate it.
#         oExcel = CreateObject("Excel.Application")
#         oBook = oExcel.Workbooks.Add
#  
#  
#         'Add data to cells of the first worksheet in the new workbook
#         oSheet = oBook.Worksheets(1)
#         oSheet.Range("A4").Value = Start1.Text
#         oSheet.Range("B4").Value = End1.Text
#         oSheet.Range("C4").Value = Time1.Text
#         oSheet.Range("D4").Value = CallType1.Text
#         oSheet.Range("E4").Value = Description1.Text
#         'etc....
#  
#  
#         'Save the Workbook and Quit Excel
#         oBook.SaveAs("C:\DailyLogs\DailyLog.xlsx")
#         oExcel.Quit()

Everything works fine as far as putting my form fields into a simple spreadsheet. But I have a spreadsheet template I need to use and print. I've been all over the internet for a few days and can't seem to find the right (VB 2010) code I need to pull up the template and fill it with my data.

One variation I did brought up the template, but it didn't fill the cells with my data- it also pulled up a blank one and filled it.

Can someone help me?

Chang your line 10 of code to

oBook = oExcel.Workbooks.Open("C:\DailyLogs\DailyLog.xlsx")

Hope this helps

Yes, that totally helped. Thanks a ton!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.