Help on Export Error "Subscript Out of Range".

[roaml]

Hi,

I am using the following code to export a query to an excel spreadsheet. I am trying to export data in three seperate tabs using a criteria dialog form (dates). This code works sometimes and sometimes I receive the following error. Can someone tell me what this error message means?

Error Message: “Subscript out of range”

I have to end process on the Excel (through the Task Manager) in order to open my Excel report. Otherwise, I receive a locked file message.

Here is the export code that I am using:

<BEGIN CODE>

Private Sub ExportRec_Click()
On Error GoTo Err_ExportRec_Click

'Open Excel report and clear existing data

Dim StrCriterion As String
Dim strDocName As String

'Select criteria to populate data

Select Case Me![Criteria]

Case 1
StrCriterion = "[enter_date] >=#" & Me![BeginDate] & "# And [enter_date] <=#" & [EndDate] & "#"
End Select

Forms!criteria_export_activity_records_dialog_form.Visible = False


'Open Excel report and clear existing data

Set myXL = CreateObject("Excel.Application")
Set myWB = myXL.Workbooks.Open("C:\Titanium_Log_Sheet_Report.xls")
myWB.Sheets("A SHIFT").UsedRange.ClearContents
myWB.Sheets("B SHIFT").UsedRange.ClearContents
myWB.Sheets("C SHIFT").UsedRange.ClearContents
myWB.Save
myXL.Quit
Set myXL = Nothing


'A SHIFT, B SHIFT AND C SHIFT

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "export_activity_A_records_qry", "C:\Titanium_Log_Sheet_Report.xls", True, "A SHIFT"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "export_activity_B_records_qry", "C:\Titanium_Log_Sheet_Report.xls", True, "B SHIFT"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "export_activity_C_records_qry", "C:\Titanium_Log_Sheet_Report.xls", True, "C SHIFT"


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

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

strMsg = "Activity records has been successfully exported!"
strTitle = "Export FSD Activity Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_ExportRec_Click:
Exit Sub

Err_ExportRec_Click:
MsgBox Err.description
Resume Exit_ExportRec_Click

End Sub

<END CODE>

Appreciate any help provided.

Thank you.

 

[PHV]

And which line of code is highlighted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[roaml]

Hi PHV,

Well, it doesn’t go into debug. It just returns a dialog with the error and locks the spreadsheet. So, I can't tell you where it is failing.