Canceling a read file function

I have a macro where the user is required to select a file to open. This is done via the Windows Open File dialog box. If they elect to "cancel" and not open the file, I get an error. I know there must be some coding I can put into my macro to trap this and just exit the macro if this happens.

Thanks
Mike

On Error GoTo Cancel

hi Mike

Use GetOpenFilename

Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)
MsgBox "your code"
wb.Close
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub



--
Regards Ron De Bruin
http://www.rondebruin.nl



"mwc0914" <[email protected]> wrote in message
news:[email protected]...
>
> I have a macro where the user is required to select a file to open. This
> is done via the Windows Open File dialog box. If they elect to "cancel"
> and not open the file, I get an error. I know there must be some coding
> I can put into my macro to trap this and just exit the macro if this
> happens.
>
> Thanks
> Mike
>
>
> --
> mwc0914
> ------------------------------------------------------------------------
> mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
> View this thread: http://www.excelforum.com/showthread...hreadid=552286
>

Sorry I forgot to paste the line:

CommonDialog1.CancelError = True

before:

On Error GoTo Cancel

"mwc0914" wrote:

>
> I have a macro where the user is required to select a file to open. This
> is done via the Windows Open File dialog box. If they elect to "cancel"
> and not open the file, I get an error. I know there must be some coding
> I can put into my macro to trap this and just exit the macro if this
> happens.
>
> Thanks
> Mike
>
>
> --
> mwc0914
> ------------------------------------------------------------------------
> mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
> View this thread: http://www.excelforum.com/showthread...hreadid=552286
>
>

Tired it but am getting False.xls not defined error. My code is...

.
.
.
Workbooks.OpenText Filename:=Application.GetOpenFilename("Text Files (*.txt), *.txt"), Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 1), _
Array(3, 9), Array(4, 1)), TrailingMinusNumbers:=True

CommonDialog1.CancelError = True
On Error GoTo Cancel
..
..
..

Cancel:
End Sub

Application.GetOpenFilename, nice! I didn't know about that function. I was
surprised I didn't need to use quotes here:

If FName <> "False" Then


"Ron de Bruin" wrote:

> hi Mike
>
> Use GetOpenFilename
>
> Sub test()
> Dim FName As Variant
> Dim wb As Workbook
> Dim MyPath As String
> Dim SaveDriveDir As String
>
> SaveDriveDir = CurDir
>
> MyPath = ThisWorkbook.Path
> ChDrive MyPath
> ChDir MyPath
>
> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
> If FName <> False Then
> Set wb = Workbooks.Open(FName)
> MsgBox "your code"
> wb.Close
> End If
>
> ChDrive SaveDriveDir
> ChDir SaveDriveDir
>
> End Sub
>
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "mwc0914" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I have a macro where the user is required to select a file to open. This
> > is done via the Windows Open File dialog box. If they elect to "cancel"
> > and not open the file, I get an error. I know there must be some coding
> > I can put into my macro to trap this and just exit the macro if this
> > happens.
> >
> > Thanks
> > Mike
> >
> >
> > --
> > mwc0914
> > ------------------------------------------------------------------------
> > mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
> > View this thread: http://www.excelforum.com/showthread...hreadid=552286
> >
>
>
>

Not use "False"

You get problems when you run the code on a Dutch machine or other language

See Stephen's site
http://www.oaltd.co.uk/ExcelProgRef/Default.htm

Click on "International Issues"


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Charlie" <[email protected]> wrote in message news:[email protected]...
> Application.GetOpenFilename, nice! I didn't know about that function. I was
> surprised I didn't need to use quotes here:
>
> If FName <> "False" Then
>
>
> "Ron de Bruin" wrote:
>
>> hi Mike
>>
>> Use GetOpenFilename
>>
>> Sub test()
>> Dim FName As Variant
>> Dim wb As Workbook
>> Dim MyPath As String
>> Dim SaveDriveDir As String
>>
>> SaveDriveDir = CurDir
>>
>> MyPath = ThisWorkbook.Path
>> ChDrive MyPath
>> ChDir MyPath
>>
>> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
>> If FName <> False Then
>> Set wb = Workbooks.Open(FName)
>> MsgBox "your code"
>> wb.Close
>> End If
>>
>> ChDrive SaveDriveDir
>> ChDir SaveDriveDir
>>
>> End Sub
>>
>>
>>
>> --
>> Regards Ron De Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "mwc0914" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > I have a macro where the user is required to select a file to open. This
>> > is done via the Windows Open File dialog box. If they elect to "cancel"
>> > and not open the file, I get an error. I know there must be some coding
>> > I can put into my macro to trap this and just exit the macro if this
>> > happens.
>> >
>> > Thanks
>> > Mike
>> >
>> >
>> > --
>> > mwc0914
>> > ------------------------------------------------------------------------
>> > mwc0914's Profile: http://www.excelforum.com/member.php...o&userid=24130
>> > View this thread: http://www.excelforum.com/showthread...hreadid=552286
>> >
>>
>>
>>

I got it working with the following code...

.
.
.
Filenamesave = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If Filenamesave = False Then
GoTo Cancel
End If

Workbooks.OpenText Filename:=Filenamesave, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 1), _
Array(3, 9), Array(4, 1)), TrailingMinusNumbers:=True
.
.
.