VBA to Extract Data from one workbook to another

[2ks]

I am looking for coding that will enable extration of data from 5 different workbooks to a central data store.

The Workbooks are named South, West, East, North West, Scotland. The other is called Data Store.

All Sheets are called data

I need the macro to run from the Data Store Workbook and ideally not to open the other workbooks.

The data on the 3 individual sheets will always start on line 5 but will need to look down to the last line of data (which is a variable) and could be different on each workbook. It will need to transfer the row to column H.

Any data transferred would need to transfer onto the Data Store workbook and start from line 5 but actually bolt on to the first empty line of data after each individual workbook.

eg.

The South workbook has 3 rows of data and would take up rows on data store (rows 5,6,7). Therefore the VBA would have to ensure data transferred of the next individual workbook would have to start on the first blank line, in this example line 8.

I dont mind how clunky the code has to be as long as I can automate from the central store workbook without opening the 5 individual workbooks

Many many thanks

 

[SkipVought]

Hi,

Check out faq68-5829.

Start on a blank sheet in your Data Store workbook. I'd recommend starting on A1 to perform the first query. You state that there are empty rows above the data: not a good strategy when designing data storage in Excel when you intend to incorporate data. I'd DELETE these empty rows so that the FIRST roe is headings, followed by data. But you can query with a criteria of NOT NULL and <> 'whatever column heading'.

The queries can be stacked one after the other, so that ALL the data comes in in one contiguous mass.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

Thanks for that SkipVought

I tried your link and as I work on a server the MSQuery function is not installed and therefore will not work.

Is there a VBA way to do it?

Cheers

 

[xlbo]

If you can;t use MSQuery then you will need to open the workbooks.

Are they all stored in 1 folder? will they always have the same names?

Based on them being in the same folder with consistent names then:

Dim wb as workbook
Const BasePath = "FullPathOfDirectoryHere"
Dim arrNames As Variant

arrNames = Array("South", "West", "East", "North West", "Scotland")

for each ArrEle in arrNames
    set wb = workbooks.open(BasePath & arrEle & ".xls")

      with WB.Sheets("Data")

          'processing here

      end with

   set wb = nothing
next

There are loads of threads in this forum relating to gathering info and putting it on the next available row - have a look, have a go and post back with any issues you have

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question