Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
list Workbooks in Current Folder
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
list Workbooks in Current Folder
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 "jC!" wrote in message om... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
list Workbooks in Current Folder
hi Chris,
exaclty what i wanted. thanks for your help and time. cheers.... ...jurgenC! remove "somewhere" from eMail when replying direct *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
list Workbooks in Current Folder
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 WLamik<atgmx.net -----Original Message----- 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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
list Workbooks in Current Folder
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use current file and folder name | Excel Discussion (Misc queries) | |||
extract from multiple workbooks in a folder | Excel Discussion (Misc queries) | |||
Macro to open most current file in folder | Excel Discussion (Misc queries) | |||
Macro syntax to open file in current explorer folder | Excel Discussion (Misc queries) | |||
Excel should open documents in the folder of the current file | Setting up and Configuration of Excel |