ps.

Instead of looping through all the files and worksheets multiple times, I think
I would use a hidden worksheet (in the workbook with the userform???).

Then depending on the number of expected files or the number of expected
worksheets per file, I'd create a table of filenames (in row 1) and worksheets
in that file (rows 2 to ###). (Or the same info transposed.)

Then I could just inspect those ranges to find the matching workbooks.



miker wrote:

Hi Dave, thanks for the example.
I have this script that kind of does what I want. (shown below)
Let me explain what it does. When I hit a button, it runs the script, it has
two list boxes, on the left, and one on the right, after it does it run, it
list's all the workbooks in a specified directory on the left listbox. When I
double click on a workbook on the left, it lists all the worksheets in the
right listbox. If i wanted to view a sheet, I would click on a button to open
the workbook up and take me to the sheet.

Now, what I want it to do now is, lets say i'm on sheet "MARCH" and I hit a
button to run the script. I want all workbooks that contain the sheet
"MARCH" to be displayed on the left lsitbox. Then when I double click the
workbook, it will just display the worksheet on the right.

is that doable?

here is the full script:

Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .List(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
wb.Sheets(oWS).Activate
Exit For
End If
Next
End With
End Sub

Private Sub CommandButton2_Click()
Set dic = Nothing
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long, w()
With Me
For i = 0 To .ListBox1.ListCount - 1
If .ListBox1.Selected(i) = True Then
w = dic.Item(.ListBox1.List(i))
With .ListBox2
.Clear
.List = w
End With
oWB = .ListBox1.List(i)
dic.Item(.ListBox1.List(i)) = w
Exit For
End If
Next
End With
End Sub
Private Sub UserForm_Initialize()
Dim FileList(), i As Long, n As Long, fName As String, shtName()
Dim wb As Workbook, ws As Worksheet

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare

FilePath = "L:\Sec09\AttendanceHistory\"
UserForm1.Caption = "List of xls files in " & FilePath
fName = Dir(FilePath & "*.xls")

On Error GoTo Xit


With Application
.ScreenUpdating = 0
.EnableEvents = 0
.DisplayAlerts = 0
End With
i = 1:
Do While fName < ""
If Not dic.exists(fName) Then
Set wb = Workbooks.Open(FilePath & fName, UpdateLinks:=0)
For Each ws In wb.Worksheets
n = n + 1
ReDim Preserve shtName(1 To n)
shtName(n) = ws.Name
Next
dic.Add fName, shtName
End If
wb.Close False: Set wb = Nothing
Erase shtName: n = 0
fName = Dir()
Loop
With Me.ListBox1
.Clear
.List = dic.keys
End With
Xit:
With Application
.ScreenUpdating = 1
.EnableEvents = 1
.DisplayAlerts = 1
End With

End Sub

--

Dave Peterson