Opening Excel files one by one from set directory 2

[OzzieOwl]

Help,

I have several excel workbooks completed by different people, I need to open all the files one by one from a specific directory, so I can combine selected parts of the workbooks. I have no problems getting the data I need, but I need help getting a file list from a directory. Does anyone know of any VBA that might help ?

Many Thanks

Martin

 

[dsi]

Look into the Dir() method or the fso (file system object). Look in VBA help or do a search in this forum or the VB 5&6 forum for a ton of examples.

 

[xlbo]

Hi Martin, try this:
Sub OpenAllFiles()
Dim filename As String
filename = Dir$("C:\Temp\*.xls&quot

Do While filename <> &quot;&quot;
Workbooks.Open (&quot;C:\Temp\&quot; & filename) 'Change path to suit
'Do stuff to wb here
filename = Dir$()
Loop
End Sub HTH
~Geoff~

 

[xlbo]

Sorry - misread the request - this will list all excel files in a specific directory:
Sub getFilenames()
Dim filename As String, r As Integer

filename = Dir$(&quot;C:\Temp\*.xls&quot
Sheets(&quot;File List&quot.Select

Range(&quot;A1&quot.Activate

Do While filename <> &quot;&quot;
ActiveCell.Offset(r, 0) = filename
r = r + 1
filename = Dir$()
Loop
End Sub
HTH
~Geoff~