Directory List of Excel Files

G

Guest

Is there a way, through VB, to get a list of excel files
from a folder and loop through that list???

I am looking at the "Directory" command but can not
figure out the syntax, and I don't think it can do want I
need anyway!

Any help would be great!!!
 
G

Guest

What you have is great! But I will only need the list of
worksheets from each workbook. Can you provide some
assistance on that ???
 
R

Ron de Bruin

Try this

Sub test2()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
J

Jake Marx

What you have is great! But I will only need the list of
worksheets from each workbook. Can you provide some
assistance on that ???
Click to expand...

The subroutine Demo below will take a folder path as an argument and return
a list of all Excel files (along with each file's worksheets) to the Debug
window. To use it, just enter this in the Debug window (or call it from
code):

Demo "C:\" '/ change this to whatever folder you want to use


Sub Demo(rsFolderPath As String)
Dim fso As Object
Dim fil As Object
Dim vWSNames As Variant
Dim v As Variant

Set fso = CreateObject("Scripting.FileSystemObject")

For Each fil In fso.GetFolder(rsFolderPath).Files
If StrComp(fil.Type, "Microsoft Excel " & _
"Worksheet", vbTextCompare) = 0 Then
Debug.Print fil.Path
vWSNames = mvGetWSNames(fil.Path)
For Each v In vWSNames
Debug.Print " " & CStr(v)
Next v
End If
Next fil

Set fso = Nothing
End Sub

Private Function mvGetWSNames(rsWBPath As String) _
As Variant
Dim adCn As Object
Dim axCat As Object
Dim axTab As Object
Dim asSheets() As String
Dim nShtNum As Integer

Set adCn = CreateObject("ADODB.Connection")
Set axCat = CreateObject("ADOX.Catalog")

With adCn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & rsWBPath & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.CursorLocation = 3
.Open
End With

Set axCat.ActiveConnection = adCn

For Each axTab In axCat.Tables
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Left$(axTab.Name, _
Len(axTab.Name) - 1)
nShtNum = nShtNum + 1
Next axTab

mvGetWSNames = asSheets

Set axCat = Nothing
adCn.Close
Set adCn = Nothing
End Function


There is no error handling, so you'd probably want to add some in the case
of a bad folder path or some unexpected error.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
G

Guest

My opologees for not being clear. I will have a summary
spreadsheet open, that will be gathering data from 400
excel files, each of which will have an unknown number of
worksheets (but each sheet will be identical in the
layout). I need to open 1 excel file at a time (your
code you pointed to me early does that!) then I need to
get a list of worksheet names from that workbook and use
that info in the other workbook I have open (name
summary.xls).

I can not figure this out as I will also have summary.xls
open and I will be opening and closing all the workbooks
one at a time to extact the data.
 
Top