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.

 

[ApplePirate]

This is what i use:-

Function importXLS_files()
Dim FileName As String
'Original command line

‘The next line should be set to match your import specifications
'DoCmd.TransferSpreadsheet acImport, "8", "Forms![File Importer]![Location]", _
&quot;->Path of files goes here<-&quot;& FileName, False, &quot;&quot;

FileName = Dir(&quot;->Path of files goes here<-&quot; _
& Forms![File Importer]![Location] & &quot;/*.xls&quot
If FileName <> &quot;&quot; Then
Do Until FileName = &quot;&quot;
DoCmd.TransferSpreadsheet acImport, &quot;8&quot;, Forms![File Importer]![Location], _
&quot;->Path of files goes here<-&quot; _
& Forms![File Importer]![Location] & &quot;/&quot; & FileName, False, &quot;&quot;
FileName = Dir()
Loop

End If
MsgBox (&quot;Import Complete!&quot


End Function
Never ever, bloody anything, ever