Using Access97 to open and populate an Excel template

[ruthcali]

i am using Office 97.

Could you please give me the code to accomplish the following:

i need some programming help in Access and help creating a command button on an Excel template.

on an Access form, the user enters some data and then clicks a button called Email.

Then, an excel template opens and certain fields from the Access form are filled into the Excel template.

Then, the Excel template is named and saved with the name of a text box on the Access form.

Then, i need Excel to stay open. I would like a command button on Excel that when clicked, opens Outlook with the TO and SUBJECT filled in And i need the Excel document to be an attachment for that email (similar to clicking File, Send to, Mail Recipient in Excel).

i have some code that does part of what i need.

Function PopulateExcel() As String

Private Const XLT_LOCATION As String = "W:\Reports\Database\Request.xlt"

On Error GoTo Populate_Err

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rs As Recordset
Dim objXL As Object, objSheet As Object, objRange As Object
Dim strSaveAs As String, strRecord As String
Dim x As Integer, intRow As Integer

DoCmd.Hourglass True
Set db = CurrentDb()

' Open, and make visible the Excel Template (Request.xlt)
Set objXL = GetObject(XLT_LOCATION)

objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True

' Open the recordset, and activate the sheet in the template
Set qdf = db.QueryDefs("qryRequest&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Set objSheet = objXL.Worksheets("TNSS&quot
objSheet.Activate
rs.MoveFirst

' Insert the data from the recordset into the worksheet

objXL.ActiveSheet.Cells(1, 5).Value = rs![Type]

objXL.ActiveSheet.Cells(3, 2).Value = rs![ProjectTitle]

' Set the save string, and save the spreadsheet. The file is saved with the project title as its name. (rs![ProjectTitle])

strSaveAs = "W:\Reports\TimingRequests\" & rs![ProjectTitle] & ".xls"
objXL.SaveCopyAs strSaveAs
PopulateExcel = strSaveAs
rs.Close

That opens the Excel template file and populates the fields in the template. It also save the Excel file in the correct folder and leaves Excel open. But, the excel file that is open and visible is the template file. I would like the newly named file to be the open that is open.
And, if the user currently had Excel open, the template doesn't open at all!!

Help please
thank you
ruth

 

[monicar]

I have a similar problem. I have an access form that looks up hyperlink addresses of microsoft word templates. The form launches the appropriate template, but I don't want the template to be saved I want a new word document to be created.

 

[ruthcali]

i figured out how to do what i wanted with Excel.

MonicaR,
this is my code to save my Excel template with the name of my project title field and put it in the correct folder:

dim strSaveAs as String
strSaveAs = "W:\Reports\TimingRequests\" & rs![ProjectTitle] & ".xls"
objXL.SaveAs strSaveAs


if you are interested, following is my complete code in Access:
the code opens an excel template, populates the fields, saves the document in the correct folder with the correct name. Then it leaves the newly named Excel workbook open. In Excel, i created a command button that opens up Outlook and lets the user email the workbook if they want.

Following is the Access code: (if you want my Excel code, let me know):

Private Const NXLT_LOCATION As String = "W:\Reports\NRequest.xlt"
Private Const CXLT_LOCATION As String = "W:\Reports\FRequest.xlt"

Sub openExcel()
On Error GoTo Populate_Err

Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rs As Recordset

Dim strSaveAs As String, strRecord As String
Dim X As Integer, intRow As Integer

Dim ObjXL As New Excel.Application


Set ObjXL = CreateObject("Excel.Application&quot
ObjXL.Visible = True

With ObjXL

If Forms![frmRequest]![Type] = "C" Then
.Workbooks.Open FileName:=CXLT_LOCATION
ElseIf Forms![frmRequest]![Type] = "N" Then
.Workbooks.Open FileName:=NXLT_LOCATION
End If

.Sheets("book1&quot.Activate


End With

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRequest&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

rs.MoveFirst

' Insert the data from the recordset into the worksheet
ObjXL.ActiveSheet.Cells(1, 4).Value = rs![Type]
ObjXL.ActiveSheet.Cells(3, 2).Value = rs![ProjectTitle]
ObjXL.ActiveSheet.Cells(5, 2).Value = rs![SubmittedDate]
ObjXL.ActiveSheet.Cells(7, 2).Value = rs![SubmittedBy]
ObjXL.ActiveSheet.Cells(7, 5).Value = rs![SubmittedByPhone]
ObjXL.ActiveSheet.Cells(14, 2).Value = rs![EquipName]
ObjXL.ActiveSheet.Cells(14, 3).Value = rs![Site1ID]
ObjXL.ActiveSheet.Cells(15, 3).Value = rs![Site2ID]
ObjXL.ActiveSheet.Cells(16, 3).Value = rs![Site3ID]

'put Display Alerts to false because if the file name already exists, Excel prompts
'the user to overwrite or not. if the user choses not to overwrite, there is a
'chance the user could mess up the excel template.
'so, the display alerts is false only while saving. this ensures the excel document
'does indeed get a different name than the excel template.
ObjXL.Application.DisplayAlerts = False
strSaveAs = "W:\Reports\Requests\" & rs![EquipName] & "\" & rs![ProjectTitle] & ".xls"

ObjXL.ActiveWorkbook.SaveAs strSaveAs

'Excel doesn't quit because the user needs it to stay open in order to allow user to email.

'set the display alerts back to true.
ObjXL.Application.DisplayAlerts = True
Set ObjXL = Nothing
Set rs = Nothing

Populate_Exit:
DoCmd.Hourglass False
Exit Sub

Populate_Err:
MsgBox Err.number & ": " & Err.Description
GoTo Populate_Exit

End Sub