How to search and open multiple excel file using vb

[Rookie591]

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...

 

[Domenic]

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:

[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]