Open file with open dialog box
 

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

Open file with open dialog box
 

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

Open file with open dialog box
 

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

Open file with open dialog box
 

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

Regards,

James

Open file with open dialog box
 

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

Open file with open dialog box
 

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