Extracting data from all files in a folder..?

[badddger]

Hey,

My problem is as follows. We are carrying out an audit of usage details of all our system users. To get this info my boss suggested I create a questionairre in Excel with two sheets; one visable sheet to populate a hidden data sheet. This was emailed out to around 200 users, the majority of which have since returned them completed.

I now have a folder containing about 170 randomly named .xls files containing data that I want to extract to a separate workbook for analysis.

I am fine with creating the VBA for extracting the data from particular cells in each individual workbook. The area I am having problems with is the code to open each file in the folder sequentially so as this data extraction can take place.

Can anyone help me with this?
Many thanks. James.

 

[LizF]

Hi James,

I asked a similar question a couple of months ago and got some superb responses (thianks Rob B and SBB!)

The thread was called "Using VBA to search filenames for namematch then take data from file" and the number was thread707-469222.

In summary of that thread, you really just need to use



Dim i as integer
Dim FileArray as Variant
Dim FolderPath as string
Dim FileName as string
Dim NoPath as string
Dim j as integer
Dim Delimiter as string

FolderPath = "put the path name for the folder you wish to search here"

With Application.Filesearch
.FileName.Execute

If .FoundFiles.Count > 0 Then
ReDim FileArray(0, .FoundFiles.Count)


'so now you have a 1 dimensional array containing the filenames (with full file path).
You might want to make the array 2D so you can strip off the file paths because once you've opened the file (using the full name with path) you'll need just the Name.xls part to close it again. To do this, make the ReDim statement above into

Redim FileArray (1, .FoundFiles.Count)


'then add in the code in red below. If you don't want to bother ignore the red code below...



For i = 1 To .FoundFiles.Count
FileArray(0, i) = .FoundFiles(i)
FileName = FileArray (0,i)

Delimiter = "\"
For j = Len(FileName) to 1 Step -1
If (Mid$(FileName, j, 1) = Delimiter) then
NoPath = mid$(FileName, j+1)
Exit For
End If
Next j

FileArray (1,i)= NoPath


'Now you can write the code to open the file (using the full name with path), copy across the stuff, then close the file using the name with no path


Hope this helps...

Liz