How to Auto Fill Excel Template

[totalnovice2]

Hi.

I have tried searching on the forum before posting but can't seem to find this (maybe because it is probably very easy).

I have info on my access form which is located here....

Forms![Front Page]![Site 2 Owner]

and here

Forms![Front Page]![Postcode S2]

I would like to export this information into cells B2 & C2( individually and respectively) on the excel spreadsheet which I have saved as a template here...

C:\Users\me\desktop\Auto\Excel Auto

Is this easily achievable? To be honest I will be using it to fill in about 12 cells but if someone could show me how it would be done for the first two i can just modify it as necessary.

Thanks in advance for your help

 

[Isskint]

Do you want to update the template or create a new document based on the template, before adding these values?

 

[totalnovice2]

I would like to update the template cells.

I will then save it as a different file name and send using outlook but I already have that code working for word so will just (I think) be able to tweak that and adapt it to the excel form using active document coding etc.


So really just asking for how to move from above access location to excel cells in the template.

Thank you.

 

[Isskint]

you would need to open the template as editable.

Workbooks.Open Filename:="ABCDE\book.xlt", Editable:=True
with that done it is, as you mentioned, just a case of automation. Add your cell values and save as template.

 

[totalnovice2]

Thank you.

So for word i use the following...

 Dim objWord As Word.Application
       'Set word as an application and make it invisible
         Set objWord = CreateObject("Word.Application")
         objWord.Visible = False 'True is visible
        
       'path and name of the template your are using.
         objWord.Documents.Add ("C:\Users\me\desktop\Auto\Access\New Access\Latest\Airwave Template1.dotm")
 

     
              objWord.ActiveDocument.Bookmarks("AirwaveSR").Select
         
             objWord.Selection.Text = Forms![Front Page]![Site 2 Owner]

So for excel would it be: changing the object to excel.application, changing the path to the file then

objexcel.activedocument.cells(C2).select

objexcel.activedocument.text = Forms![Front Page]![Site 2 Owner]

?????

It is just the starting off that I can't get my head around ha.

Thanks again.

 

[Isskint]

err, no.

have a look at the examples here where Bob demonstrates interacting with Excel. In essence you create an object to represent Excel, another object to represent the workbook (template). with these you can then access the spreadsheet and do what you want.

 

[totalnovice2]

Thanks for all of your help - i have bookmarked the site you sent for future reference.

I managed to work it out using the below code instead though: it might be helpful if anyone else comes across this thread.

Private Sub Command154_Click()
 
On Error Resume Next
 
'Declare the follwing
Dim excelApp As Excel.Application
Dim excelWorkbook As Excel.Workbook
Dim excelWorkSheet As Excel.Worksheet
 
'Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
 
excelWorkbook = excelApp.Workbooks.Open("C:\Users\me\desktop\Auto\Access\New Access\Latest.xlt")
excelApp.Visible = True
excelWorkSheet = excelWorkbook.Sheets("Permit Request Form")
 
With excelWorkSheet
 
.Range("B3").Value = Forms![Front Page]![txtTelNo]
 
'in the brackets, the first number represents the row and the second number represents the column so (3, 3) is actually C3
 
.Cells(3, 3).Value = Forms![Front Page]![Site 2 Owner]
.Cells(3, 4).Value = Forms![Front Page]![Start Date]