Depending on which directory a user picks his file, the processing routines
will vary. This means I would need to be able to evaluate the path of the
file that was chosen and run the appropriate subroutine. I would also like to
be able to evaluate the filename as well. The problem I am having is that the
GetOpenFilename returns the path and filename together. Ideally I'd like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.

Hi Bob,
The middle one is what your looking for.

Debug.Print ThisWorkbook.FullName, ThisWorkbook.Name, ThisWorkbook.Path

John

"Bob Zimski" wrote in message ...
Depending on which directory a user picks his file, the processing routines
will vary. This means I would need to be able to evaluate the path of the
file that was chosen and run the appropriate subroutine. I would also like to
be able to evaluate the filename as well. The problem I am having is that the
GetOpenFilename returns the path and filename together. Ideally I'd like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.

Not sure what to do with the below. Seems no matter what I do, it keeps
giving back the information for the Personal.xls file. I presume perhaps
there is something I am supposed to do beforehand?



"jaf" wrote:

Hi Bob,
The middle one is what your looking for.

Debug.Print ThisWorkbook.FullName, ThisWorkbook.Name, ThisWorkbook.Path

John

"Bob Zimski" wrote in message ...
Depending on which directory a user picks his file, the processing routines
will vary. This means I would need to be able to evaluate the path of the
file that was chosen and run the appropriate subroutine. I would also like to
be able to evaluate the filename as well. The problem I am having is that the
GetOpenFilename returns the path and filename together. Ideally I'd like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.

Just to clarify, I need to evaulate the directory and path 'before' opening
the file because the importing of the data structure depends on where it is
coming from and what it is called. Once I know this, then I can open it with
appropriate import parameters.


"Bob Zimski" wrote:

Depending on which directory a user picks his file, the processing routines
will vary. This means I would need to be able to evaluate the path of the
file that was chosen and run the appropriate subroutine. I would also like to
be able to evaluate the filename as well. The problem I am having is that the
GetOpenFilename returns the path and filename together. Ideally I'd like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.

Try code like


Dim FName As Variant
Dim N As Long
Dim FileName As String
Dim PathName As String

FName = Application.GetOpenFilename()
If FName = False Then
' user cancelled
Exit Sub
End If

N = InStrRev(FName, "\")
PathName = Left(FName, N - 1)
FileName = Mid(FName, N + 1)
Debug.Print PathName, FileName


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Tue, 24 Feb 2009 08:15:01 -0800, Bob Zimski
wrote:

Depending on which directory a user picks his file, the processing routines
will vary. This means I would need to be able to evaluate the path of the
file that was chosen and run the appropriate subroutine. I would also like to
be able to evaluate the filename as well. The problem I am having is that the
GetOpenFilename returns the path and filename together. Ideally I'd like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.

You want this to happen after the dialog, before opening the file ?

Sub test()
Dim V As Variant, S As String
V = Application.GetOpenFilename
If V = False Then Exit Sub
S = CStr(V)
MsgBox S
MsgBox Left$(S, InStrRev(S, "\") - 1)
MsgBox Mid$(S, InStrRev(S, "\") + 1)
End Sub


HTH. Best wishes Harald

"Bob Zimski" wrote in message
...
Not sure what to do with the below. Seems no matter what I do, it keeps
giving back the information for the Personal.xls file. I presume perhaps
there is something I am supposed to do beforehand?



"jaf" wrote:

Hi Bob,
The middle one is what your looking for.

Debug.Print ThisWorkbook.FullName, ThisWorkbook.Name, ThisWorkbook.Path

John

"Bob Zimski" wrote in message
...
Depending on which directory a user picks his file, the processing
routines
will vary. This means I would need to be able to evaluate the path of
the
file that was chosen and run the appropriate subroutine. I would also
like to
be able to evaluate the filename as well. The problem I am having is
that the
GetOpenFilename returns the path and filename together. Ideally I'd
like to
break it into two strings to evaluate both of them before proceeding.

Any help appreciated.