Receive error on export code that has been used before.

[roaml]

Hello,

I am having problems using the following code to export records to an Excel file. I have used this code in the past and it works great, but now it is giving me an error "Variable not defined". I am receiving the error on "myXL" and "myWB". I have not received this error on the same code used elsewhere. Maybe a new set of eyes can catch it.

Thanks.

<BEGIN CODE>
Private Sub Export_TT_Records_Click()
On Error GoTo Err_Export_TT_Records _Click

Dim StrCriterion As String
Dim strDocName As String

'Open Excel report and clear existing data

Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\Project_Trouble_Ticket_Report.xls")
myWB.Sheets("OTHER").UsedRange.ClearContents
myWB.Save
myXL.Quit
Set myXL = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "sev_other_tt_qry", "C:\Project_Trouble_Ticket_Report.xls", True, "OTHER"


' MISSAGE DIALOG BOX -- Display message when data export is completed.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Project records have been successfully exported!"
strTitle = "Export Project Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_Export_TT_Records_Click:
Exit Sub

Err_Export_TT_Records_Click:
MsgBox Err.Description
Resume Exit_Export_TT_Records_Click

End Sub
<END CODE>

 

[RoyVidar]

Well, in this code, they are not declared. Perhaps where you copied this from, you had them as module level or public (global) variables?

Roy-Vidar

 

[roaml]

Hi Roy,

I'm using the same code on a switchboard menu and it works great. Unfortunately, I am unable to locate a module or other global variables relating to this code.

I tried declaring the Excel App and Workbook. It runs but when I attempt to open my Excel file it crashes on me. Am I not declaring these correctly?

Thanks, I apprecite your assistance.

<BEGIN CODE>
Private Sub Export_TT_Records_Click()
On Error GoTo Err_Export_TT_Records _Click

Dim StrCriterion As String
Dim strDocName As String
Dim myXL As Object
Dim myWB As Object

'Open Excel report and clear existing data

Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\Project_Trouble_Ticket_Report.xls")
myWB.Sheets("OTHER").UsedRange.ClearContents
myWB.Save
myXL.Quit
Set myXL = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "sev_other_tt_qry", "C:\Project_Trouble_Ticket_Report.xls", True, "OTHER"


' MISSAGE DIALOG BOX -- Display message when data export is completed.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Project records have been successfully exported!"
strTitle = "Export Project Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_Export_TT_Records_Click:
Exit Sub

Err_Export_TT_Records_Click:
MsgBox Err.Description
Resume Exit_Export_TT_Records_Click

End Sub
<END CODE>