Hi
I let user to open his/her own file with this VBA......

Sub ConsolFile()
Application.Workbooks.Open (GetFileName)
End Sub

Function GetFileName()
'Get a FileName (may change current drive/directory.)
Filename = Application.GetOpenFilename
'Get the current directory and drive
TempDir = CurDir()
'Switch back to original drive
ChDrive Mid(TempDir, 1, 1)
'Switch back to the orginal directory
ChDir TempDir
'Return the filename
GetFileName = Filename
End Function

It doesn't work 'cause it return false when user cancel....How can I fix
this problem?

Thanks
Jafery

Hi Jafery,

Somewhere with in either your function/module you just need to say if
Getfilename = false then...

With in your module you could write:

If Getfilename = False then
Msgbox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileName)
End if

Regards,

James

James... Thank you very much...

last time I put

getfilename ="False" ...cause I think the function return character....
and it doesn't work

Jafery

" wrote:

Hi Jafery,

Somewhere with in either your function/module you just need to say if
Getfilename = false then...

With in your module you could write:

If Getfilename = False then
Msgbox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileName)
End if

Regards,

James

Yeah, it was returning a boolean result rather than a string hence no
"" were required.

Regards,

James

Hi James

I have been trying this little script. It runs through fine but then wishes
to restart from the beginning but then end once it has opened the file.

This is what I have. Except for my very long script.

Sub ConsolFileMulti()
Application.Workbooks.Open (GetFileNameMulti)
End Sub

Function GetFileNameMulti()
'Get a FileName (may change current drive/directory.)
Filename = Application.GetOpenFilename
'Get the current directory and drive
TempDir = CurDir()
'Switch back to original drive
ChDrive Mid(TempDir, 1, 1)
'Switch back to the original directory
ChDir TempDir
'Return the filename
GetFileNameMulti = Filename
If GetFileNameMulti = False Then
MsgBox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileNameMulti)

End If

' the start of a really long script


€˜ end of the really long script

End Function

What am I doing to cause the loop.

TIA Regards Noel

" wrote:

Hi Jafery,

Somewhere with in either your function/module you just need to say if
Getfilename = false then...

With in your module you could write:

If Getfilename = False then
Msgbox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileName)
End if

Regards,

James

When I tested your code, it didn't cause a loop. But I suspect that it depends
on what's in that really long script.

Are you trying to open a few files in the same folder (consolfilemulti kind of
sounds like it)?

You could use something like:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wkbk As Workbook

myFileNames = Application.GetOpenFilename _
(filefilter:="Excel Files, *.xls", MultiSelect:=True)

If IsArray(myFileNames) Then
For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
'do what you want here
wkbk.Close savechanges:=False
Next iCtr
End If

End Sub

If you're allowing the user to chose a file (any folder) and continue until they
cancel, I'd use something like:

Option Explicit
Sub ConsolFileMulti()
Dim myFileName As Variant
Do
myFileName = GetFileNameMulti
If myFileName = False Then
Exit Do
Else
Application.Workbooks.Open myFileName
'call the long loop here
End If
Loop
End Sub
Function GetFileNameMulti() As Variant
Dim myCurFolder As String
myCurFolder = CurDir
GetFileNameMulti = Application.GetOpenFilename
ChDrive myCurFolder
ChDir myCurFolder
End Function

Using a line like "End" (not "End Sub" or "End Function" or "End if") is not a
nice way to exit a routine. If you have public variables, you'll lose their
contents.



NoelH wrote:

Hi James

I have been trying this little script. It runs through fine but then wishes
to restart from the beginning but then end once it has opened the file.

This is what I have. Except for my very long script.

Sub ConsolFileMulti()
Application.Workbooks.Open (GetFileNameMulti)
End Sub

Function GetFileNameMulti()
'Get a FileName (may change current drive/directory.)
Filename = Application.GetOpenFilename
'Get the current directory and drive
TempDir = CurDir()
'Switch back to original drive
ChDrive Mid(TempDir, 1, 1)
'Switch back to the original directory
ChDir TempDir
'Return the filename
GetFileNameMulti = Filename
If GetFileNameMulti = False Then
MsgBox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileNameMulti)

End If

' the start of a really long script

€˜ end of the really long script

End Function

What am I doing to cause the loop.

TIA Regards Noel

" wrote:

Hi Jafery,

Somewhere with in either your function/module you just need to say if
Getfilename = false then...

With in your module you could write:

If Getfilename = False then
Msgbox "No File was selected, the macro will now end"
End
Else
Application.Workbooks.Open (GetFileName)
End if

Regards,

James



--

Dave Peterson