How to search and open multiple excel file using vb

Rookie591

New Member
Joined
Jun 25, 2011
Messages
1
Hi,
i'm still REALLY novice in this field and needs your help to solve my problem.
i want to create system that can search and open 1 excel file from multiple excel file.Since there are too many excel file, i create a database(using excel) which list all the excel file and sorting it by project name in which i want to retrieve the excel file through project name.
this is the example database that i had create:


ProjectName ExcelFile
PA201 d:\Project\PA201.xlsx


i just want to design 1 user interface,type the project name in the textbox then click command button to open the excel file but i don't know the coding....huhuhu~:(


can u help me?...


thanks in advance...
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Assumptions:

1) Sheet2, Columns A and B contain the database.

2) Sheet1 contains a TextBox from the ActiveX controls.

2) Sheet1 contains a Button from the Forms controls to which the following code can be assigned.

Macro:

Code:
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] OpenFile()

    [color=darkblue]Dim[/color] WS1 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] WS2 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] ProjName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] FileName [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=darkblue]Set[/color] WS1 = Worksheets("Sheet1") [color=green]'change the sheet name accordingly[/color]
    [color=darkblue]Set[/color] WS2 = Worksheets("Sheet2") [color=green]'change the sheet name accordingly[/color]
    
    ProjName = WS1.OLEObjects("TextBox1").Object.Value [color=green]'change the TextBox name accordingly[/color]
    
    [color=darkblue]If[/color] ProjName = "" [color=darkblue]Then[/color]
        MsgBox "Please enter a project name...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]With[/color] WS2
        [color=darkblue]Set[/color] FoundCell = .Columns("A").Find(what:=ProjName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]If[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MsgBox "No such project name was found...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    FileName = FoundCell.Offset(, 1).Value
    
    [color=darkblue]If[/color] FileName = "" [color=darkblue]Then[/color]
        MsgBox "No filename was found...", vbExclamation
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]If[/color] Dir(FileName) <> "" [color=darkblue]Then[/color]
        Workbooks.Open FileName
    [color=darkblue]Else[/color]
        MsgBox "No such workbook exists...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top