VBA to open an external excel file and direct to the specific spreadsheet

[zaizhi]

Dear all,

This is my first post and need your kind help. I am totally new in VBA.

Here is the case:

I want the user to input 3 things : workbook name, spreadsheet name, password. Then Click on an active button, the required workbook will open, and display the required spreadsheet.

Below is the code i have, but it can only open the specified workbook, not able to direct to the specified spreadsheet. Please help. Thank you all.

Private Sub CommandButton2_Click()

Dim varFNamePath As Variant
Dim colFoundFiles As New Collection
Dim strFilter As String
Dim strPath As String
Dim myRow As Long

strPath = "C:\Documents and Settings\zaizhi zhang\Desktop\test"

strFilter = "*" & Sheets("menu").Range("E17").Value & "*.*"

Call FileSearch(colFoundFiles, strPath, strFilter, True)

If colFoundFiles.Count = 0 Then
    MsgBox "No file was found!"
    Exit Sub

End If

ActiveWorkbook.FollowHyperlink Address:=colFoundFiles(1)
Application.CommandBars("Web").Visible = False

End Sub


Private Sub FileSearch(colFoundFiles As Collection, strPath As String, _
    strMask As String, blnIncSubDir As Boolean)

Dim strDirFile As String
Dim varColItem As Variant
Dim colSubDir As New Collection

strPath = Trim(strPath)
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

strDirFile = Dir(strPath & strMask)
Do While strDirFile <> ""
    colFoundFiles.Add strPath & strDirFile
    strDirFile = Dir
Loop

If Not blnIncSubDir Then Exit Sub

strDirFile = Dir(strPath & "*", vbDirectory)
Do While strDirFile <> ""
    If strDirFile <> "." And strDirFile <> ".." Then If ((GetAttr(strPath & strDirFile) And _
        vbDirectory) = 16) Then colSubDir.Add strPath & strDirFile
    strDirFile = Dir
Loop

For Each varColItem In colSubDir
     Call FileSearch(colFoundFiles, CStr(varColItem), strMask, blnIncSubDir)
Next


End Sub

[snb]

sub snb()
  c00 = "C:\Documents and Settings\zaizhi zhang\Desktop\test.xls"
  c01="menu"
  c02= "E17"

  if dir(c00)<>"" then
    with workbooks.open(c00)
      .application.goto .sheets(c01).range(c02)
    end with
  end if
end sub