Click here to Skip to main content
15,789,360 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have imported a data into excel sheet. I want to make my imported data up to date by refreshing data. At this step, Dialogbox will show and ask user to select the file which I want to refresh and I have to choose a file for refreshing and click "Import" Button. but I dont want user see the path file. Thus I create a command button for user to refresh the data. My question is what is the query to make this process run autometically
Maciej Los 27-Jun-13 4:25am    
What have you done so far? Where are you stuck? What version of MS Excel?

1 solution

It's simple ;)

Steps to do:
1) Go to Code window (ALT+F11)
2) Insert new UserForm (use Insert->UserForm menu)
3) Add:
- ListBox (at the top of UserForm area)
- 2 Buttons (one for Cancel action and one for OK action)
4) Set UserForm properties:
- Caption = "Select file..."
5) Set ListBox properties:
- BackColor = &H8000000F& (Button Face)
- BorderStyle = fmBorderStyleNone
- ListStyle = fmListStyleoption
- MultiSelect = fmMultiSelectSingle
- SpecialEffect = fmSpecialEffectFlat
6) Set Button1 properties:
- Name = CmdCancel
- Caption = "Cancel"
- Cancel = True
7) Set Button2 properties:
- Name = CmdOK
- Caption = "OK"
8) Copy and paste below code to UserForm1 module:
Option Explicit

Private Sub CmdCancel_Click()
Unload Me
End Sub

Private Sub CmdOK_Click()

MsgBox "Selected file: '" & Me.ListBox1.Value & "'", vbInformation, "Information..."

Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer

With Me.ListBox1
    .ColumnCount = 2
    .ColumnWidths = .Width - 24 & ";0" '
    .BoundColumn = 2
    For i = 1 To 3
        .AddItem ""
        .Column(0, .ListCount - 1) = "ShortFileName_" & i
        .Column(1, .ListCount - 1) = "FullFileName_" & i
    Next i
End With

End Sub

9) Inside ImportButton_Click event add this line:

10) Test it!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900