Append from muliple workbooks (1 Viewer)

[DanG]

Append from multiple workbooks

I have looked around and can't seem to find a solution...

I have a folder with many workbooks that are all layed out the same, with 1 worksheet that has three columns of data with varied row counts.

I would like to copy each workbooks contents starting at cell A2 (copy all 3 columns) and paste it into a master workbook and append them all onto one worksheet in the master workbook.

I hope that makes sense?

There is a lot of information out there on combining workbooks into one, but they all add the worksheets themselves into the master worksheet.

Thank you

 

[the_net_2.0]

Dan,

I suppose you could use ADO to do this, but DAO would be just as good I would guess. Why couldn't you create a master workbook, and then simply open the other 3, book by book and loop the sheets' ranges, starting at cell A2? Or, if you use ADO, you could simply query the sheets' ranges starting at cell A2. If you do that though, you'll have to name the ranges in all 3 workbooks.

an example maybe of ADO would be something like:

Dim cnStr As String
Dim cn As ADODB.Connection
Dim cnRs As ADODB.Recordset
Set cn = New ADODB.Connection
Set cnRs = New ADODB.Recordset

   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strSource & ";" & _
                          "Extended Properties=" & Chr(34) & "Excel 8.0;" & iHeaders & Chr(34)
      .Open
   End With

      cnRs.Open "SELECT * FROM [" & RangeName & "$]", cn
      
            Do Until .EOF
               
               For ctr = 0 To cnRs.Fields.Count - 1
                  [COLOR="Red"]'COPY THE VALUES ON OVER RIGHT HERE[/COLOR]
               Next ctr
               
                     .MoveNext
                  
            Loop

where 'strSource' is the path of each of your excel files that you want to consolidate. That example was written in Access, so it might need a little change. The 'copyfromrecordset' method might also be available. If it is, you wouldn't have to loop.

 

[DanG]

Thank you 2.0!

I did find an excel solution that works very well and wanted to get back to others who may be looking to do this same thing.

This solution however is an add-in, so I will disect it later to learn from it. But I must say it works VERY well.

Here's the link to the add-in along with a blurb on it...
[URL="http://www.rondebruin.nl/merge.htm[/URL]

It will combine all workbooks (and worksheets in them if you choose to) into a single worksheet based on your criteria.

I think there is much to be learned by disecting it.