Copying data to another workbook

[Kenny Bones]

So, I've got about 300 Excel workbook which all are basically the same. Now, I want the value from cell B48 and B54 to be copied to a separate workbook, a summary workbook, at a specific line which contains a text string that match the string from cell B6 in each workbook.

What would be the best approach to do this?
I thought, I could probably add a macro to each of the Workbooks at their Save handler which basically opens the other workbook, copies in the values, saves the workbook and then exits the macro and the workbook alltogether

But is there any other way of doing this? Would it be easier to have all the 300 workbooks in their own sheets? And then have one extra sheet that works as a summary sheet? Collecting data from all 300 sheets?

[Domski]

Hi,

As a one off if all the workbooks are saved in a folder it's quite straightforward to write some code which will loop through them all and grab the info you want as long as they are consistent with regards to populated cells, sheet names etc.

Dom

[Kenny Bones]

Yes they are all identical in layout etc. But that would mean a macro that collects the values I need in strings (name, value1 and value2), opens the summary workbook, search for the name string, adds value1 and value2 in the correct cells and then saves the workbook.

How can I search for a text string in the most efficient manner?

[Domski]

I'll pop an example together.

Dom

[Domski]

Not tested this but hopefully it works and makes sense.

Please Login or Register  to view this content.

The code would go in a module within the summary workbook which should sit in a different directory to the other workbooks.

Dom

[Kenny Bones]

I get a run-time 91 - Object variable or With block variable not set at this line:

Please Login or Register  to view this content.

Any idea why that is?

Btw: Which document should this code be added to anyway? This code should be added in each Excel document?

[Domski]

No, just in the summary workbook...

The code would go in a module within the summary workbook which should sit in a different directory to the other workbooks.

Make sure you change the sheet names in the code as well.

Dom

[Kenny Bones]

Hmm, not sure if I understand. So basically, whenever one makes changes to any of the other 300 Workbooks, how does the summary workbook get the new data then? If no macro is fired off from the other workbook?

Edit: Do you mean the this macro should be triggered from the Summary Workbook? Wouldn't that take an insane amount of time? Opening each 300 Workbooks, one after another?

[Domski]

Edit: Do you mean the this macro should be triggered from the Summary Workbook? Wouldn't that take an insane amount of time? Opening each 300 Workbooks, one after another?

Yes, that's correct.

Depends on the size of the workbooks and how long it takes to open them but if we turned off screen updating it probably wouldn't take more than a few mins.

Dom

[Domski]

I've added code to turn off screen updating and set calculation to manual while it runs which will speed things up.

Please Login or Register  to view this content.

Dom

[Kenny Bones]

Hmm, I still get an error on the line:

Please Login or Register  to view this content.

In this block:

Please Login or Register  to view this content.

Isn't is possible to just use ThisWorkbook.Sheets(1) or something? So I don't have to specify the name of the sheet? There are only one sheet in each workbook anyway. But the error seems to be with the Range I believe.

[Domski]

My fault, it should be:

Please Login or Register  to view this content.

And yes you can use:

Please Login or Register  to view this content.

To pick up the info from the first (or only) sheet in each workbook.

Dom

[Kyle123]

How about not opening the files at all, maybe something like the below?

I haven't tested it, but it *should* work

Please Login or Register  to view this content.

[Kenny Bones]

That seems promising
But I still don't see why the macro should be triggered from the Summary workbook. Wouldn't it be a lot easier to just have a macro triggered at the Save Handler in each workbook instead? Pseudo code:

Please Login or Register  to view this content.

[Kyle123]

You could do that, however, this would break if someone already has the workbook open, as the client workbook wouldn't be able to save any changes. I suppose you could get round this with a third workbook which all 300 workbooks write to and then close. Your summary workbook could then pull from this

[Kenny Bones]

Something's happening to the strings when I pick them up via ADODB connection. This really doesn't make any sense.

Ok, I collect the values in the function and pass them back. Then, I search for the first string in the array and I get no matches. But, If I manually assign the Var(0) to be the exact same as it was before, everything works just fine!

Here's a screenshot: http://img641.imageshack.us/img641/4043/unledozi.png

So, something is happening here. Here's my full code that's a bit altered from your last one. Notice how I specifically collect only the three cells I want the value from in that function you created. Then I merge these to an array, which is passed back to the initial sub.

Please Login or Register  to view this content.

Edit: What's really strange here is that if I take the value from Var(0) and assign that value to one of the cells in the Excel sheet and then re-run the code, it can find it!
Even though it looks exactly the same. I just use "ws.Cells(5,1).Value = Var(0)" and see that the string is the exact same. But somehow Var(0) differs. I've also checked if there are any white spaces and there are none. Also, both cells are formatted exactly the same.

[Kyle123]

hmm, when I tried your code it returned a trailing unknown character for the string, I did this to get round it and it seems to work:

Please Login or Register  to view this content.

[Kenny Bones]

That does seem to do the trick! What exactly do you do here? You create a temporay variable and assign the first string from the array to that temp variable? Why would that do the trick exactly?

[Kyle123]

I haven't looked into it so I might not be right! But I suspect it's to do with the way tha the GetString function returns its results - possibly a delimeter to the end. I used a different function GetRows which returns the data in an array.

Glad it's working