Export to Excel TransferSpreadsheet

[deltagracesun]

Hi All,
Don't ask many questions here. Usually putt through it till I find out myself but this one has got me. This code worked yesterday and doesn't today! Using Microsoft Access 2007 but saved as 2003 version (due to NMCI to enable security requirements). The error appears at "Dim objXLBook As Excel.Workbook". Any quick help is appreciated. Thanks in advance. R/Dawn

Private Sub QueryData_Click()
On Error GoTo Err_QueryData_Click

DoCmd.SetWarnings off
Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim xlApp As Excel.Application

If Forms![frmFurloughOverComptime].[Type] = "O/T" Then

If DCount("*", "PullOvertimeAppr") = 0 Then
MsgBox "There are no records to report based on your input."
Else
Set objXLApp = CreateObject("Excel.Application")
conPath = "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\280\Databases\
destPath = "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\Overtime\"
' conFileName = "280FurloughOvertime" & Format(Me.EnterDate, "mm") & Format(Me.EnterDate, "dd") & Format(Me.EnterDate, "yy") & ".xlsx"
'delete the existing spreadsheet
Kill conPath & "280FurloughOvertime.xlsx"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "FurloughOvertimeTemplate.xlsx")
objXLBook.SaveAs (conPath & "280FurloughOvertime.xlsx")
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"PullOvertimeAppr", conPath & "280FurloughOvertime.xlsx", True

Kill destPath & "280FurloughOvertime.xlsx"

Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "280FurloughOvertime.xlsx")
objXLBook.SaveAs (destPath & "280FurloughOvertime.xlsx")
objXLBook.Close

MsgBox "Complete! Press 'OK' to open the (Read-Only) File for review. Use the 'Save As' function to save the file in the pre-selected directory and add the appropriate date at the end of the filename. Thank you."

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\Overtime\280FurloughOvertime.xlsx", True, True
Set xlApp = Nothing

End If
End If

If Forms![frmFurloughOverComptime].[Type] = "C/T" Then

If DCount("*", "PullOvertimeAppr") = 0 Then
MsgBox "There are no records to report based on your input."
Else
Set objXLApp = CreateObject("Excel.Application")
conPath = "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\280\Databases\"
destPath = "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\Overtime\"
' conFileName = "280FurloughOvertime" & Format(Me.EnterDate, "mm") & Format(Me.EnterDate, "dd") & Format(Me.EnterDate, "yy") & ".xlsx"
'delete the existing spreadsheet
Kill conPath & "280FurloughComptime.xlsx"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "FurloughComptimeTemplate.xlsx")
objXLBook.SaveAs (conPath & "280FurloughComptime.xlsx")
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"PullOvertimeAppr", conPath & "280FurloughComptime.xlsx", True

Kill destPath & "280FurloughComptime.xlsx"

Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "280FurloughComptime.xlsx")
objXLBook.SaveAs (destPath & "280FurloughComptime.xlsx")
objXLBook.Close

MsgBox "Complete! Press 'OK' to open the (Read-Only) File for review. Use the 'Save As' function to save the file in the pre-selected directory and add the appropriate date at the end of the filename. Thank you."

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "\\naeanrfkfs41\C162\USFF_NRFK_N42158_16AA_2\NSSA\Shared\280\Overtime\280FurloughComptime.xlsx", True, True
Set xlApp = Nothing
End If
End If

DoCmd.Close acForm, "frmFurloughOverComptime"

Exit_QueryData_Click:
Exit Sub
Err_QueryData_Click:
MsgBox Err.Description
Resume Exit_QueryData_Click
End Sub

 

[deltagracesun]

Solved. Had to add a Office 12 reference. Thanks.