list Workbooks in Current Folder

[jC!]

hi all,

your help is mostly appreciated for following:

i have a dedicated folder (called "Project" for example) which has
following Workbooks:

Workbook1
Workbook2
Workbook3
and most important
MasterWorkbook

now i would like to have a macro which is executed in the
MasterWorkbook that will generate the list of all the Workbooks in the
folder where the MasterWorkbook resides ("Project" folder in this
example) except for the MasterWorkbook itself on a specific sheet
(called "Workbook Overview" for example).

in this example on MasterWorkbook.Workbook Overview the list would be
as follow:
Workbook1
Workbook2
Workbook3

thanking you for your time - cheers...


....jurgenC!

 

[Chris Putzig]

Try this, it assumes your "MasterWorkbook" is the ActiveWorkbook :

Sub listProjectFiles()
Sheets.Add
myFile = Dir(ActiveWorkbook.Path & "\*.xls")
i = 1
Do While myFile <> ""
If myFile <> ActiveWorkbook.Name Then
Cells(i, 1) = myFile
i = i + 1
End If
myFile = Dir
Loop
End Sub

....Chris

 

[jurgenC!]

hi Chris,

exaclty what i wanted.

thanks for your help and time.

cheers....


...jurgenC!

remove "somewhere" from eMail when replying direct

 

[wolf]

Hi jurgenC

something like that (not very elegant, but it seeems to
work):

Sub FindAllWorkbooks()

Dim strBook As String

ThisWorkbook.Worksheets("Workbook Overview").Select
Range("a1").Select
strBook = Dir("c:\project\*.xls", vbDirectory)
ActiveCell.Value = strBook
Do While strBook <> ""
strBook = Dir
If strBook <> "MasterWorkbook" Then
ActiveCell.Value = strBook
ActiveCell.Offset(1, 0).Select
End If
Loop

'if you want to sort it
Range("a:a").Sort key1:=Range("a1")
Range("a1").Select

End Sub


Best regards

Wolf

 

[jurgenC!]

Hi Wolf,

thanks for your solution and especially the sorting routine.

find following the code which i use now for my Worksheet:

Sub listProjectFiles()
Dim myFile As String
Dim i As Integer
myFile = Dir(ActiveWorkbook.Path & "\*.xls")
i = 1
Do While myFile <> ""
If myFile <> ActiveWorkbook.Name Then
If myFile Like "TestCas*(*" Then
Range("TestCasesOverview").Offset(i, 0) = myFile
i = i + 1
End If
End If
myFile = Dir
Loop
End Sub

this works like a charm, if for example the folder has follwoing XLS
files:
TestPlan
TestCases (test)
TestCases template
and i execute (from TestPlan the mastersheet), i receive following list:
TestCases (test)

- i am somewhat perplexed though when trying to shorten the above code
to following:

Sub listProjectFiles()
Dim myFile As String
Dim i As Integer
myFile = Dir(ActiveWorkbook.Path & "\*.xls")
i = 1
Do While myFile <> ""
If myFile <> ActiveWorkbook.Name or myFile <> "*template" Then
Range("TestCasesOverview").Offset(i, 0) = myFile
i = i + 1
End If
myFile = Dir
Loop
End Sub

i then receive following list:
TestCases (test)
TestCases template

however, i am trying to omit 'TestCases template'

it is not a major concern but would be great to understand where my
logic is taking a wrong turn

cheers....

....jurgenC!

remove "somewhere" from eMail when replying direct