I would like to store the path of a file in a variable. I have read
the help associated with Excel but I am not clear on the use of the
PATH command. I understand how to get the path of the current
workbook but what I want to do is to store the path of a file (which I
have obtained using the GETOPENFILENAME CODE) in a variable for later
use. Any suggestions?

Garry

Sub test()
Dim strFile As String

strFile = Application.GetOpenFilename
If strFile = "False" Then Exit Sub

MsgBox strFile
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
om...
I would like to store the path of a file in a variable. I have read
the help associated with Excel but I am not clear on the use of the
PATH command. I understand how to get the path of the current
workbook but what I want to do is to store the path of a file (which I
have obtained using the GETOPENFILENAME CODE) in a variable for later
use. Any suggestions?

Garry

Rob,

Thanks for the reply but that wasn't exactly what I needed. I already
used code similar to what you suggested to get the full path name,
including filename. What I need is to find the actual path, without
the filename. I need to iteratively retrieve files in the same
directory as one located by the code you suggested. I want to capture
the path alone then concatenate various filenames to that path.

Garry

"Rob van Gelder" wrote in message ...
Sub test()
Dim strFile As String

strFile = Application.GetOpenFilename
If strFile = "False" Then Exit Sub

MsgBox strFile
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
om...
I would like to store the path of a file in a variable. I have read
the help associated with Excel but I am not clear on the use of the
PATH command. I understand how to get the path of the current
workbook but what I want to do is to store the path of a file (which I
have obtained using the GETOPENFILENAME CODE) in a variable for later
use. Any suggestions?

Garry

Here's yet another path extractor (there was a conversation about this the
other day.
Keep in mind there are easier ways to do this depending on your version of
Excel.

Sub test()
Dim strFile As String, strPath As String, i As Long, j As Long

strFile = "C:\windows\system32\drivers\etc\HOSTS"

i = InStr(1, strFile, Application.PathSeparator): j = 1
Do Until i = 0: j = i: i = InStr(i + 1, strFile,
Application.PathSeparator): Loop
strPath = Mid(strFile, 1, j)

MsgBox strPath
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
m...
Rob,

Thanks for the reply but that wasn't exactly what I needed. I already
used code similar to what you suggested to get the full path name,
including filename. What I need is to find the actual path, without
the filename. I need to iteratively retrieve files in the same
directory as one located by the code you suggested. I want to capture
the path alone then concatenate various filenames to that path.

Garry

"Rob van Gelder" wrote in message
...
Sub test()
Dim strFile As String

strFile = Application.GetOpenFilename
If strFile = "False" Then Exit Sub

MsgBox strFile
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
om...
I would like to store the path of a file in a variable. I have read
the help associated with Excel but I am not clear on the use of the
PATH command. I understand how to get the path of the current
workbook but what I want to do is to store the path of a file (which I
have obtained using the GETOPENFILENAME CODE) in a variable for later
use. Any suggestions?

Garry

Rob,

Thanks for the information. I'll give this a try. I used a mid
function after retrieving the full name with the GetOpenFileName
method. I had hoped there was a more straight forward way to do it.
You plan looks better, even though not much less complex.

Thanks again,

Garry


"Rob van Gelder" wrote in message ...
Here's yet another path extractor (there was a conversation about this the
other day.
Keep in mind there are easier ways to do this depending on your version of
Excel.

Sub test()
Dim strFile As String, strPath As String, i As Long, j As Long

strFile = "C:\windows\system32\drivers\etc\HOSTS"

i = InStr(1, strFile, Application.PathSeparator): j = 1
Do Until i = 0: j = i: i = InStr(i + 1, strFile,
Application.PathSeparator): Loop
strPath = Mid(strFile, 1, j)

MsgBox strPath
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
m...
Rob,

Thanks for the reply but that wasn't exactly what I needed. I already
used code similar to what you suggested to get the full path name,
including filename. What I need is to find the actual path, without
the filename. I need to iteratively retrieve files in the same
directory as one located by the code you suggested. I want to capture
the path alone then concatenate various filenames to that path.

Garry

"Rob van Gelder" wrote in message
...
Sub test()
Dim strFile As String

strFile = Application.GetOpenFilename
If strFile = "False" Then Exit Sub

MsgBox strFile
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Garry" wrote in message
om...
I would like to store the path of a file in a variable. I have read
the help associated with Excel but I am not clear on the use of the
PATH command. I understand how to get the path of the current
workbook but what I want to do is to store the path of a file (which I
have obtained using the GETOPENFILENAME CODE) in a variable for later
use. Any suggestions?

Garry

I really liked the code that Rob posted - it covers EVERYTHING.

I typically use the following which is far less sophisticated but
hasn't failed me yet:

Sub test()
Dim strFile As String, strPath As String, strEndofPath As String

strFile = Application.GetOpenFilename
strEndofPath = InStr(strFile, Dir(strFile)) - 2
strPath = Mid(strFile, 1, strEndofPath)

MsgBox strPath
End Sub




Sub test()
Dim strFile As String, strPath As String, i As Long, j As Long

strFile = "C:\windows\system32\drivers\etc\HOSTS"

i = InStr(1, strFile, Application.PathSeparator): j = 1
Do Until i = 0: j = i: i = InStr(i + 1, strFile,
Application.PathSeparator): Loop
strPath = Mid(strFile, 1, j)

MsgBox strPath
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel