This is what I have come up woth so far. Will this work?
Option Compare Database
Option Explicit
Dim Cdb As Database
Dim PathStr As String
Dim TempPath As String
Dim LogoPath As String
'****************************************************************************************************************************************
Public Sub Embed_Check_Request_InExcel(RequestNbr As String, Optional LeadNbr As String)
Dim objXLSheet As Excel.Workbook
Dim Rs As Recordset
Dim sqlStr As String
Dim PMCstr As String
Dim txtProgram As String
Dim FilePath As String
Dim PMCFName As String
Dim PMCLName As String
On Error GoTo PROC_ERR
Set Cdb = CurrentDb
sqlStr = "SELECT tblDFLModified.[Check Request Date], tblDFLModified.RequestNbr, tblDFLModified.Payee, tblDFLModified.[Mailing Address]," & _
"tblDFLModified.City , tblDFLModified.State, tblDFLModified.[Zip Code], tblDFLModified.Amount, tblDFLModified.PMC," & _
"tblDFLModified.Ref_Type, tblDFLModified.FileNbr, tblDFLModified.[Buy/Sell Price], tblDFLModified.Program'" & _
"FROM tblDFLModified WHERE (((tblDFLModified.[Check Request Date])=[enter date]));"
Set Rs = Cdb.OpenRecordset(sqlStr)
If Rs.EOF Then
MsgBox "No Record found"
Exit Sub
End If
DoCmd.SetWarnings False
'If the template file has moved or renamed, then ask the user's help to locate the file.
If Not IsNull(DLookup("[DocPath]", "DocPath")) Then
FilePath = DLookup("[DocPath]", "DocPath") & "\Templates\CheckRequest.xls"
Else
FilePath = GetOpenFile_TSB("C", "Find Excel File Template")
End If
If Not FileExists(FilePath) Then
FilePath = GetOpenFile_TSB("C", "Find Excel File Template")
End If
If IsNull(FilePath) Then
MsgBox "Couldn't find the Excel Sheet Template"
Exit Sub
End If
Set objXLSheet = GetObject(FilePath)
objXLSheet.Application.Visible = True
objXLSheet.Windows(1).Visible = True
If Len(Nz(Rs.Fields("Amount"), "")) > 0 Then
.Cells(7, 3) = Rs.Fields("Amount")
Else
.Cells(7, 3) = "---"
End If
If Len(Nz(Rs.Fields("Payee"), "")) > 0 Then
.Cells(8, 2) = Rs.Fields("Payee")
Else
.Cells(8, 2) = "---"
End If
If Len(Nz(Rs.Fields("Mailing Address"), "")) > 0 Then
.Cells(9, 2) = Rs.Fields("Mailing Address")
Else
.Cells(9, 2) = "---"
End If
If Len(Nz(Rs.Fields("City"), "")) > 0 Then
.Cells(10, 2) = Rs.Fields("City")
Else
.Cells(10, 2) = "---"
End If
If Len(Nz(Rs.Fields("State"), "")) > 0 Then
.Cells(10, 4) = Rs.Fields("State")
Else
.Cells(10, 4) = "---"
End If
If Len(Nz(Rs.Fields("Zip Code"), "")) > 0 Then
.Cells(10, 5) = Rs.Fields("Zip Code")
Else
.Cells(10, 5) = "---"
End If
If Len(Nz(Rs.Fields("RequestNbr"), "")) > 0 Then
.Cells(21, 3) = Rs.Fields("RequestNbr" & "/")
Else
.Cells(21, 3) = "---"
End If
If Len(Nz(Rs.Fields("Ref_Type"), "")) > 0 Then
.Cells(21, 5) = Rs.Fields("Ref_Type" & "/")
Else
.Cells(21, 5) = "---"
End If
If Len(Nz(Rs.Fields("Buy/Sell Price"), "")) > 0 Then
.Cells(24, 1) = Rs.Fields("Buy/Sell Price")
Else
.Cells(24, 1) = "---"
End If
If Len(Nz(Rs.Fields("Program"), "")) > 0 Then
.Cells(25, 1) = Rs.Fields("Program")
Else
.Cells(25, 1) = "---"
End If
End Sub