Copy different ranges from several sheets from closed Workbook

[sampson32]

I’ve started with Ron de Bruin’s “Copy a range from closed workbooks (ADO)” to access and pull data from a closed workbook. (It has a VBA sequence password that makes it difficult to open programmatically)

Using the “GetData” routine below…

****************************

Sub GetData_Example3()

GetData "\\CCServer01\cc apps\Branch Count.xls", "Tampa", "B8:N12", Sheets("Total Count").Range("E5"), False, False

End Sub

****************************


This code works as expected – it pulls the values from the “Branch Count Workbook / Tampa sheet / range “B8:N12” and places the copied range in the Active Workbook on sheet “Total Count” starting in cell E5.

I actually need this code to go a little further…

I need to copy 2 separate ranges from 10 different sheets in the Branch Count Workbook and deposit the copied values in 2 seperate ranges on the ‘Total Count’ sheet.

Something like this – using the above code I stared with…

Copy values from the Branch Count Workbook on sheet “Tampa” the separate ranges B8:N12 and T6:T16 and have the values deposited on the active sheet “Total Count” starting in ranges E5 and X5 respectively.

The 2 separate range areas of each sheet that has values copied and the 2 seperate range areas to populate on the Total Count spreadsheet are not identical.

Is there a way to get the “GetData” code to handle more than 1 sheet and 1 range?

Thanks...

 

[Norie]

Why not just run the GetData again with different parameters instead of rewriting the function?

 

[sampson32]

From my limited knowledge it seems the "GetData" calls and runs the ADO -the focus then goes back to the start (GetData).

do I need to run the complete ADO code for each seperate GetData line of code that would be created or is there a way to integrate the several GetData's with the same ADO code.

I hope I'm clear on my question?

Thanks...

 

[Norie]

I've not seen the code for the function - I think you need to download a workbook for that but I would assume it does all the ADO stuff.

So you don't need to worry about that part.

What you do need is a way to 'easily' pass the parameters to the function for multiple workbooks/sheets/ranges.

You could probably do that with some sort of array/arrays.

Or perhaps create a list on a worksheet of the source workbooks/sheets/ranges and the destinations.

Then you would loop through either, get the parameters and call the function with the parameters.