Populating Excel Template

[Tom from Sacto]

Hello all, first time posting. I'm looking for some help with VBA; I am attempting to set up a command button to transfer data from an access record to an excel template. The best I can do is get the excel template to open with the "click" but it will not populate any cells. I have read and read all sorts of solutions, tried the ones I understood, but I'm not getting anywhere. Below is the code that I am using. Suggestions?


Private Sub Command717_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("Q:\Access Work\Access Test 2.xlsx")
objXLApp.Application.Visible = True
objExcelBook.ActiveSheet.Range("C3") = Me.[Project Name]
objExcelBook.ActiveSheet.Range("D3") = Me.[Project #]
End Sub
Private Sub Open_New_Financail_Record_2_Click()
End Sub

 

[Isskint]

One solution would be to import the data to your template by means of an import query, instead of sending it to it. Create another tab that you import the data to, include a method to select the record to print (Menu option, combo box on template tab etc) and then include code to refresh the query on workbook open (if you want things to happen automatically that is).
If you want to have the spreadsheet open from Access then use the Application.FollowHyperlink to accomplish this.

 

[Tom from Sacto]

I did put it into a query and I'm a bit confused as how I tell the transaction find the information from the query. I hate to be a bother but can you also clue me in on getting the query to automatically update. Thanks you.

 

[Isskint]

All the work here is done on your Excel template spreadsheet.

1. Create a new tab that will import all the data from your database (lets call the tab dbDATA)
   1. Create an Excel import query to the database (Data>>Import External Data>>New Database Query>>MS Access Database>>Browse for your database)
2. On the Template tab, enter formulas to pull the necessary data from dbDATA. If you only import one record at a time then it would be simple.
   If you have multiple records then you will need to include a way to indicate which record you wish to display/print on the template. One way would be to use a row reference and have all the formulas use INDEX and MATCH to get the data from the correct row. Another option would be to set up some VBA to loop through each row.
3. To update automatically, put code in the Workbook Open event
   1. Sheets(sheetname).Select
      ActiveSheet.QueryTables("queryname").Refresh BackgroundQuery:=False