Run-time error 1004
 

I am using the following to retreive the name of a file that i wish to open.
If i select 'Cancel' rather than a file name and 'Open', i would receive a
Run-time error 1004. I am handling this potential error using the ErrorClose
routine to allow me to 'Retry'. If i select the option to retry and then
elect not to open a file for a second time i receive the Run-time error 1004
message which is not handled by the ErrorClose.

Why would the error be handled as expected on the first pass but not on the
second?

Thanks



Step1:

RawDataFileInfo = "Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsx),*.xlxs"
RawDataFilterIndex = 1
RawDataTitle = "Select Raw Data File to Import"
RawDataFileName = Application.GetOpenFilename(RawDataFileInfo,
RawDataFilterIndex, RawDataTitle)
On Error GoTo ErrorClose


"Rest of Code" .............................


Exit Sub

ErrorClose:

If Err.Number = 1004 Then
ErrMsg = "You must select an Outstanding Stock Order to import" &
vbNewLine & vbNewLine
ErrReply = MsgBox(ErrMsg, vbExclamation + vbRetryCancel, "Invalid
Action")
If ErrReply = vbRetry Then
GoTo Step1
Else
Application.ActiveWorkbook.Close
End If
End If

End Sub

Run-time error 1004
 

Try the below

Dim rawdatafilename As Variant

Do
RawDataFileInfo = "Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsx),*.xlxs"
RawDataFilterIndex = 1
RawDataTitle = "Select Raw Data File to Import"
rawdatafilename = Application.GetOpenFilename(RawDataFileInfo, _
RawDataFilterIndex, RawDataTitle)

If rawdatafilename = False Then
If MsgBox("You must select an Outstanding Stock Order to import", _
vbExclamation + vbRetryCancel, "Invalid Action") < vbRetry Then _
Application.ActiveWorkbook.Close: Exit Sub
End If
Loop While rawdatafilename = False
MsgBox "File selected"
End Sub

--
Jacob (MVP - Excel)


"Confused Slug" wrote:

I am using the following to retreive the name of a file that i wish to open.
If i select 'Cancel' rather than a file name and 'Open', i would receive a
Run-time error 1004. I am handling this potential error using the ErrorClose
routine to allow me to 'Retry'. If i select the option to retry and then
elect not to open a file for a second time i receive the Run-time error 1004
message which is not handled by the ErrorClose.

Why would the error be handled as expected on the first pass but not on the
second?

Thanks



Step1:

RawDataFileInfo = "Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsx),*.xlxs"
RawDataFilterIndex = 1
RawDataTitle = "Select Raw Data File to Import"
RawDataFileName = Application.GetOpenFilename(RawDataFileInfo,
RawDataFilterIndex, RawDataTitle)
On Error GoTo ErrorClose


"Rest of Code" .............................


Exit Sub

ErrorClose:

If Err.Number = 1004 Then
ErrMsg = "You must select an Outstanding Stock Order to import" &
vbNewLine & vbNewLine
ErrReply = MsgBox(ErrMsg, vbExclamation + vbRetryCancel, "Invalid
Action")
If ErrReply = vbRetry Then
GoTo Step1
Else
Application.ActiveWorkbook.Close
End If
End If

End Sub

Run-time error 1004
 

Or use the FileDialog as below...

Dim strFile As String

Do
With Application.FileDialog(msoFileDialogOpen)

..AllowMultiSelect = False
..Filters.Add "Excel Files", "*.xls", 1
..InitialFileName = "C:\"
..Show

If .SelectedItems.Count = 0 Then
If MsgBox("No file selected", vbRetryCancel) < vbRetry Then Exit Sub
Else
strFile = .SelectedItems(1): Exit Do
End If

End With
Loop

MsgBox "File selected"

--
Jacob (MVP - Excel)


"Confused Slug" wrote:

I am using the following to retreive the name of a file that i wish to open.
If i select 'Cancel' rather than a file name and 'Open', i would receive a
Run-time error 1004. I am handling this potential error using the ErrorClose
routine to allow me to 'Retry'. If i select the option to retry and then
elect not to open a file for a second time i receive the Run-time error 1004
message which is not handled by the ErrorClose.

Why would the error be handled as expected on the first pass but not on the
second?

Thanks



Step1:

RawDataFileInfo = "Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsx),*.xlxs"
RawDataFilterIndex = 1
RawDataTitle = "Select Raw Data File to Import"
RawDataFileName = Application.GetOpenFilename(RawDataFileInfo,
RawDataFilterIndex, RawDataTitle)
On Error GoTo ErrorClose


"Rest of Code" .............................


Exit Sub

ErrorClose:

If Err.Number = 1004 Then
ErrMsg = "You must select an Outstanding Stock Order to import" &
vbNewLine & vbNewLine
ErrReply = MsgBox(ErrMsg, vbExclamation + vbRetryCancel, "Invalid
Action")
If ErrReply = vbRetry Then
GoTo Step1
Else
Application.ActiveWorkbook.Close
End If
End If

End Sub

Run-time error 1004
 

Thanks Jacob, all sorted



"Jacob Skaria" wrote:

Or use the FileDialog as below...

Dim strFile As String

Do
With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xls", 1
.InitialFileName = "C:\"
.Show

If .SelectedItems.Count = 0 Then
If MsgBox("No file selected", vbRetryCancel) < vbRetry Then Exit Sub
Else
strFile = .SelectedItems(1): Exit Do
End If

End With
Loop

MsgBox "File selected"

--
Jacob (MVP - Excel)


"Confused Slug" wrote:

I am using the following to retreive the name of a file that i wish to open.
If i select 'Cancel' rather than a file name and 'Open', i would receive a
Run-time error 1004. I am handling this potential error using the ErrorClose
routine to allow me to 'Retry'. If i select the option to retry and then
elect not to open a file for a second time i receive the Run-time error 1004
message which is not handled by the ErrorClose.

Why would the error be handled as expected on the first pass but not on the
second?

Thanks



Step1:

RawDataFileInfo = "Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsx),*.xlxs"
RawDataFilterIndex = 1
RawDataTitle = "Select Raw Data File to Import"
RawDataFileName = Application.GetOpenFilename(RawDataFileInfo,
RawDataFilterIndex, RawDataTitle)
On Error GoTo ErrorClose


"Rest of Code" .............................


Exit Sub

ErrorClose:

If Err.Number = 1004 Then
ErrMsg = "You must select an Outstanding Stock Order to import" &
vbNewLine & vbNewLine
ErrReply = MsgBox(ErrMsg, vbExclamation + vbRetryCancel, "Invalid
Action")
If ErrReply = vbRetry Then
GoTo Step1
Else
Application.ActiveWorkbook.Close
End If
End If

End Sub