I currently have code listed below in the form of a Microsoft Access application that copies an Excel Spreadsheet from a centralized template and pastes a new copy of it to a local location for the user to enter data on for a current incident, then save it or e-mail it without corrupting the original template. A lot of the data requested in the Excel template is already in my Access DB and I have written a query (qry_Investigate) that asks the user what incident# they are working with and creates a local table (local_tbl_Investigate_Tem
p) isolating this information for them. I would like to incorporate into my code the ability to export the information from this Access table into the second worksheet of the opened local Excel workbook (F:\FM Accident Invest Write-in.xls) from Sheet2!A1:Sheet2!P1 (16 data items), then have the template on Sheet1 of the workbook populate the associated fields so the end user does not have to re-type data already stored in the DB.
Please note: I use this code for a lot of forms that I pull from a central location. So, I have a table (tbl_Path) that stores the locations of each template.
Private Sub Run_Report_Click()
Dim db As Database
Dim path_RST As Recordset
Dim path_STR As String
Dim image1_Src As String
Dim image1_Dst As String
Dim fname1 As String
Dim pfname1 As String
Dim msg As Integer
Dim msg2 As Integer
Set db = CurrentDb
Set path_RST = db.OpenRecordset("tbl_Path", dbOpenDynaset)
Set fs = CreateObject("Scripting.FileSystemObject")
image1_Src = path_RST![path]
image1_Dst = "F:\"
fs.CopyFile image1_Src, image1_Dst
fname1 = Right(path_RST![path], (Len(path_RST![path]) - 30))
pfname1 = image1_Dst & fname1
msg = MsgBox("The copy of this spreadsheet you are viewing has been saved to your (F:) drive and named: " & fname1, vbOKOnly, "Working Copy")