Thank you for this, but I keep getting a runtime 3265 error - "Item cannot be
found in the collection corresponding to the requested name or ordinal".

It keeps stopping for debug at the code:

rsOut!first = Rs!first

Thanks anyway.

"urkec" wrote:

Hi,

I think you can use something like this


Sub getData()

'On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

'put all the file names without extension
'into an array
initials = Array("XX", "XY", "XZ", "XA")

'overwrite existing combined.xls
Application.DisplayAlerts = False

'add header rows to combined.xls
Set combined = Workbooks.Add
With combined.Sheets(1)
.Cells(1, 1) = "first"
.Cells(1, 2) = "second"
.Cells(1, 3) = "third"
.Cells(1, 4) = "fourth"
End With

'save and close combined.xls
combined.SaveAs "S:\data\combined.xls"
combined.Close

'connect to combined.xls using ADO
Set CnnOut = CreateObject("ADODB.Connection")
Set rsOut = CreateObject("ADODB.Recordset")

CnnOut.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\data\combined.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

rsOut.Open "Select * FROM [Sheet1$]", _
CnnOut, adOpenStatic, adLockOptimistic, adCmdText

'loop through initials array
'to construct the appropriate file name
'XX.xls, XY.xls ...
For Each initial In initials

Set Cnn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")

Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\data\" & initial & ".xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

'get the data from each .xls file
Rs.Open "Select * FROM [Sheet1$]", _
Cnn, adOpenStatic, adLockOptimistic, adCmdText

'write the data to combined.xls
Do Until Rs.EOF

rsOut.AddNew
rsOut!first = Rs!first
rsOut!Second = Rs!Second
rsOut!third = Rs!third
rsOut!fourth = Rs!fourth
rsOut.Update
Rs.MoveNext

Loop

'clean up for the next .xls file
Set Rs = Nothing
Set Cnn = Nothing

Next

'clean up for combined.xls
Set rsOut = Nothing
Set CnnOut = Nothing

End Sub


--
urkec


"Nav" wrote:

Hello

I have 10 files (1 per person- S:\data\(initials)) they have the same format
(1st four columns) with data in them with the first row as a header. I wish
to create 1 file for all this data, is there an easy way to obtain the data
from all the files and create a new one under S:\data\combined without having
to open then all 1 by one and copy and insert the cells into the new combined
file (the data range also changes on a day to day basis).

Please can you provide suggestions, thank you in advance for your help.

Regards.