Browse For a File or Path and enter in a cell

[Guest]

Is it posssible in Excel 2003 to Browse to either select a file name or path
that would be entered in a cell for reference or used in a macro?

 

[Dave Peterson]

You could use:

Dim myFileName as variant
myfilename = application.getopenfilename
if myfilename = false then
'use hit cancel
else
activesheet.range("a1").value = myfilename
end if

But I'm not sure what you're really asking.

 

[Steve Yandl]

Here is an option that will give you a browse window for files with the
initial focus on the 'My Documents' folder. You probably want to do
something more interesting than have a message box pop up with the path and
file name for each selected item though.

__________________________________

Sub UserGetMyDocsFiles()

Dim fd As FileDialog
Dim vrtSelectedItem As Variant

' Determine Path to the My Documents folder
Set objShell = CreateObject("Shell.Application")
Set objMyDocsFldr = objShell.Namespace(&H5&)
strMyDocsPath = objMyDocsFldr.Self.Path

' Create a file picker dialog opening to My Documents
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
..Filters.Clear
..InitialFileName = strMyDocsPath
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox vrtSelectedItem
Next vrtSelectedItem
End If
End With

Set objShell = Nothing
Set fd = Nothing

End Sub

___________________________________

Steve Yandl

 

[Guest]

I will try to make my question clearer.

I would like to enter a file name in a cell and the path to that file name
in another to use later is a couple of macros.

I would like to use a browse type window (Like in Excel to find a "Save As"
directory location) to select the file and path to that file and have them
entered into a cell in Excel.

 

[Dave Peterson]

Dim myFileName as string
dim teststr as string

with thisworkbook.worksheets("somesheetname")
myfilename = .range("a1").value & "\" & .range("b1").value
end with

teststr = ""
on error resume next
teststr = dir(myfilename)
on error goto 0

if teststr = "" then
msgbox "That file doesn't exist!
else
'do your stuff
end if

I don't understand the second portion of your question. If you put the name of
the file in a cell and the path in a different cell, then don't you already know
the location of the file and folder?

 

[Steve Yandl]

Here is a modified version of the sub I posted earlier. This time, when the
user selects files and clicks 'OK', each file name is appended to Column A
and the corresponding path to the selected file is placed in Column B.

______________________________________

Sub UserGetMyDocsFiles()

Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim R As Integer
Dim strPathName As String
Dim strFileName As String

R = Range("A65536").End(xlUp).Row + 1

' Determine Path to the My Documents folder
Set objShell = CreateObject("Shell.Application")
Set objMyDocsFldr = objShell.Namespace(&H5&)
strMyDocsPath = objMyDocsFldr.Self.Path

' Create a file picker dialog opening to My Documents
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
..Filters.Clear
..InitialFileName = strMyDocsPath
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
arrFullName = Split(vrtSelectedItem, "\")
strPathName = ""
strFileName = ""
strFileName = arrFullName(UBound(arrFullName))
For X = 0 To UBound(arrFullName) - 1
strPathName = strPathName & arrFullName(X) & "\"
Next X
Cells(R, 1).Value = strFileName
Cells(R, 2).Value = strPathName
R = R + 1
Next vrtSelectedItem
End If
End With

Set objShell = Nothing
Set fd = Nothing

End Sub
_____________________________________

Steve Yandl