Searching other forums I was able to get help and piece together the correct code. I am posting it below
Thank you again for all of your help!
Private Sub NEKMH310_Click()
On Error Resume Next
Dim sCriteria As String
Dim db As Database
Dim rst As Recordset
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim strTemplatePath As String
Dim sOutput As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb()
'construct the SELECT and FROM clauses of the query
mySQL = "SELECT [10t_KMH310_Data].COID, ([Post_Date]+1) AS Rpt_Date, [10t_KMH310_Data].Dept_ID AS DEPT, "
mySQL = mySQL & " [10t_KMH310_Data].Dept_Description AS DEPT_NAME, [10t_KMH310_Data].Pt_Name, [10t_KMH310_Data].Pt_Num AS PT_ACCT, "
mySQL = mySQL & " [10t_KMH310_Data].CDM, [10t_KMH310_Data].CDM_Description AS CDM_DESC, [10t_KMH310_Data].Post_Date, "
mySQL = mySQL & " [10t_KMH310_Data].Trans_Date, [10t_KMH310_Data].Qty, [10t_KMH310_Data].PT, [10t_KMH310_Data].RVU, "
mySQL = mySQL & " [10t_KMH310_Data].Amount, [10t_KMH310_Data].Batch "
mySQL = mySQL & " FROM [10t_KMH310_Data]"
'construct the WHERE Clause
If Nz(Me.Post_Date, "") = "" Then
MsgBox "You must enter a post date"
Me.Post_Date.SetFocus
Exit Sub
Else
sCriteria = sCriteria & " [10t_KMH310_Data].POST_DATE = #" & Me.Post_Date & "#"
End If
'add the WHERE Clause to the mySQL variable
mySQL = mySQL & " WHERE" & sCriteria
Debug.Print mySQL
'proceed with opening the recordset
Set rst = db.OpenRecordset(mySQL)
'This is new
strTemplatePath = "W:\HER\HER - Administration\revops\RevOps_Databases\Monarch\ROSS_Uploads\01 - Daily\Excel Templates\NE_EKMH310 (2010-02-23).xlt" ' template file reference
sOutput = "W:\HER\HER - Patient Access Management\PasCoord\Process Improvement Team\Jason\Revops\NE_eKMH310" & " (" & Format([Post_Date] + 1, "yyyy-mm-dd") & ").xls" 'output file name and path set up to automatically save the file with the POST_Date +1
'This is new
Set objApp = New Excel.Application
'This is new 'Your excel spreadsheet file goes here
Set objBook = objApp.Workbooks.Add(strTemplatePath)
'Name of sheet you want to export to
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("KMH310")
objBook.Windows(1).Visible = True
'Opens the recordset and sets the variable
'Set rst = db.OpenRecordset("10q_Daily_eKMH310_Charge_Data (NE)")
With objSheet
.Select
'Clears the current contents in the workbook range
.Range("A9:O65000").ClearContents
'rst Copies the recordset into the worksheet
.Range("A9").CopyFromRecordset rst
End With
objBook.SaveAs (sOutput)
objBook.Close
rst.Close
objApp.Visible = False
Set rst = Nothing
Set db = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
MsgBox "NE eKMH310 has been published"
End Sub