VBA for Popup to select a file

[blnswamy]

Hi
I am working on a project where i need to create a macro and give it to a user. The task is to create a popup window so that the user can select a file from a folder in their PC.
Thank you

 

[wigi]

Did you search the web for GetOpenFilename ?

 

[blnswamy]

Hi I am not familiar with this function.

 

[Smitty]

Welcome to the Board!

As Wigi mentioned, you can use the GetOpenFileName method. Here's some code that will do it:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GOFNam()<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,

 

[blnswamy]

Thank you very much for the code. it works fantastically.
I am building a macro to give it a user so that they can load two files and compare.
I want to know if i should use a excel macro-enabled template or just a macro enabled workbook. should i be using active x controls or form controls

 

[Smitty]

How you save it is up to you. If you don't want users to be able to alter the original and force them to save as a new workbook each time, then a template is a great way to go.

As for ActiveX vs. Form controls, the former are more robust, but you need to write code for them individually in each worksheet module, so they don't lend themselves to being copied to other sheets. With Form controls you assign macros to them, so you can copy them to various sheets and they'll retain their functionality. They each have different, although similar purposes, so it's really just a matter of preference. I prefer ActiveX controls, but I'm comfortable writing the code for them. If you're just using buttons to run code, then Forms are fine, although for that I prefer to use Shapes, because you have much richer formatting options.

 

[blnswamy]

Thank you very much for you help Smitty
I want to know which command to use if i want to want the first sheet of the file which was opened in a new sheet in my current workbook. is Worksheets.Active= Opened file

 

[Smitty]

I want to know which command to use if i want to want the first sheet of the file which was opened in a new sheet in my current workbook. is Worksheets.Active= Opened file

I'm not sure what you're asking?

 

[blnswamy]

So my question is -
Let us say we are opening Test01.xls.
we have a macro which will enable the user to select and open a file.
the goal is open the file and copy the Sheet1 of Test01.xls to Book1(Sheet1)[book1 is the default name of the any excel file. it means the current file which has the macro enabled in it. ]

Test02("Sheet1").Copy
ACtiveSheet.Paste Destination:= Worksheets("Sheet1")


is this correct. ?/

 

[blnswamy]

To my earlier message- this is the macro

Sub Afterfile()
Dim AfterFileName As Variant
Dim Filt As String, Title As String
Dim FilterIndex As Integer, Response As Integer

' Set Drive letter
ChDrive "C:\"
' Set to Specified Path\Folder
' ChDir "C:\Documents and Settings\All Users\Desktop\"
' Set File Filter
Filt = "Excel Files (*.xml), *.xml, "
' Set *.* to Default
FilterIndex = 5
' Set Dialogue Box Caption
Title = "Please select the second File"
' Get FileName
AfterFileName = Application.GetOpenFilename(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)
' Exit if Dialogue box cancelled
If AfterFileName = False Then
Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
Exit Sub
End If
' Display Full Path & File Name
Response = MsgBox("You selected " & AfterFileName, vbInformation, "Proceed")
' Open Selected Workbook
Worksheets.Add Before:=Sheets(1), Count:=1
AfterFileName("Sheet1").Copy -----this is where there is a problem
ActiveSheet.Paste Destination:=AfterFileName("Sheet1")




End Sub

So my question is -
Let us say we are opening Test01.xls.
we have a macro which will enable the user to select and open a file.
the goal is open the file and copy the Sheet1 of Test01.xls to Book1(Sheet1)[book1 is the default name of the any excel file. it means the current file which has the macro enabled in it. ]

Test02("Sheet1").Copy
ACtiveSheet.Paste Destination:= Worksheets("Sheet1")


is this correct. ?/