Run-time error 1004
in [Programming]
|
From: Confused Slug on 3 Jun 2010 09:12 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
From: Jacob Skaria on 3 Jun 2010 10:17 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
From: Jacob Skaria on 3 Jun 2010 11:54 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
From: Confused Slug on 4 Jun 2010 02:58 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
|
Pages: 1 Prev: What equation that i need to use to group in vba Next: How to deliver an Excel tool training |