Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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. D. Jones wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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 "D. Jones" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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" wrote: 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. D. Jones wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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? D. Jones wrote: 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" wrote: 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. D. Jones wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Browse For a File or Path and enter in a cell
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 "D. Jones" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using UNC path instead of URL to link cell to PDF file | Excel Worksheet Functions | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Browse File for Mac | Excel Discussion (Misc queries) | |||
Browse button on form for folder path | Excel Discussion (Misc queries) | |||
can't browse file | Excel Discussion (Misc queries) |