Can you define a source file?

[levifunk]

I'd like to be able to create an "import data" button, but I can't be certain where users have stored the data. Is there a way that a user would be able to identify the source file when clicking that button?

Ideally it would work the same way Excel has me select which file I want to open when I click File>Open.

 

[Smitty]

You can use the GetOpenFilename method, which will let a user browse to a file location, then capture the name they select:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetOpenFileName()<br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">'   Set Drive letter</SPAN><br>        ChDrive "C:\"<br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir "C:\Documents and Settings\All Users\Desktop\"<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Excel Files (*.xls), *.xls"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetOpenFileName(FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Open Selected Workbook</SPAN><br>        Workbooks.Open FileName<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[VoG]

Take a look at Application.GetOpenFilename

 

[levifunk]

Thanks for the quick response Smitty. thats what I was looking for!

Although, I found this to be unnecessary:

   '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\Documents and Settings\All Users\Desktop\"