Vba list filenames in a folder into an array

QuestionEdit

, I have current code in which I have to directly specify workbooks that I want to search through. Instead of having my users re-enter this into the code each time. I want to take the path of the masterWorkbook (The one the macro is in) and use its path to search for other workbooks in the same directory. In fact it may be easier to take the master workbooks path (Lets say C:\Project\MasterBook.xlsm and then knowing the MasterBook.xlsm file is in C:\Project\ search C:\Project\Workbooks for all of the other workbooks. I want to get the names of all workbooks in the "Workbooks" folder and read them into an array. The following is my existing code, I'd like to use the same variable names, etc just make the program find the workbooks itself instead of me having to specify them.

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 'Open all needed Workbooks

mNumberOfWorkbooks = 3 ReDim mWorkbooks(1 To mNumberOfWorkbooks)

   '/////////////////////////////////////////////
   'YOU MUST SPECIFY YOUR WORKBOOK LOCATIONS HERE
   '/////////////////////////////////////////////
   mWorkbooks(1) = "P:\Telesis Numbers\Suspension Welder D-Side Data.xls"
   mWorkbooks(2) = "P:\Telesis Numbers\SW 3E macro telesis 6-17-10.xls"
   mWorkbooks(3) = "P:\Telesis Numbers\SW2A penetration 6-15-10.xls"

mWorkbookCounter = 1 Do While mWorkbookCounter < 4

   Workbooks.Open Filename:=mWorkbooks(mWorkbookCounter)
   mWorkbookCounter = mWorkbookCounter + 1

Loop

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

AnswerEdit

so if the workbook with the code is in C:\Project, then you want to put all the workbooks that exists in a subdirctory of the folder and the name of that subdirectory is workbooks so look in C:\Project\Workbooks\

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 'Open all needed Workbooks

Dim mWorkbooks As Variant Dim mNumberofWorkbooks As Long Dim mWorkbookCounter As Long Dim sPath As String Dim sName As String ReDim mWorkbooks(1 To 1) mNumberofWorkbooks = 0

sPath = ThisWorkbook.Path If Right(sPath, 1) <> "\" Then sPath = sPath & "\" sPath = sPath & "Workbooks\" sName = Dir(sPath & "*.xls") Do While sName <> ""

mNumberofWorkbooks = mNumberofWorkbooks + 1
ReDim Preserve mWorkbooks(1 To mNumberofWorkbooks)
mWorkbooks(mNumberofWorkbooks) = sPath & sName
sName = Dir()

Loop mWorkbookCounter = 1 Do While mWorkbookCounter < mNumberofWorkbooks

  Workbooks.Open Filename:=mWorkbooks(mWorkbookCounter)
  mWorkbookCounter = mWorkbookCounter + 1

Loop

End Sub '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

of course you could just open them rather than putting the names in an array and then opening them -

Sub ABC()

Dim mNumberofWorkbooks As Long Dim sPath As String Dim sName As String ReDim mWorkbooks(1 To 1) mNumberofWorkbooks = 0

sPath = ThisWorkbook.Path If Right(sPath, 1) <> "\" Then sPath = sPath & "\" sPath = sPath & "Workbooks\" sName = Dir(sPath & "*.xls")

Do While sName <> ""

workbooks.Open filename:= sPath & sName
mNumberofWorkbooks = mNumberofWorkbooks + 1
sName = Dir()

Loop

End Sub

but maybe you need the array for later work.