Get Data From other Excel Workbook

[t0m46]

Hey all,

Currently i have macro that opends up 10 different Workbooks, copies a range of data from each Workbook, goes back to the main Workbook, and Pastes it into that workbook.

I have got it working but it is really slow.

is there a way to get data from a sheet in a workbook without having to open that sheet and copy and paste? eg set up a macro for import external data?

any help would be great

Thanks

Tom

[royUK]

Don't Select or Activate the Workbooks. It would be best to post your code

[t0m46]

Hey,

here is the main part of the code I have.

Please Login or Register  to view this content.

[foxguy]

Hi t0m46;
I have commented out the obvious sections that slow it down. As Roy says, it's unnecessary to "Select" or "Activate" workbooks, sheets, etc. about 99% of the time.
I don't really see anything that is really time consuming unless you still have "ScreenUpdating" on.

Please Login or Register  to view this content.

[t0m46]

Hey,

Thanks that code works really well. I dont no if a specific sheet is making it slow as the sheet i want to paste the data in is really slow even if i select a cell. the worksheet is empty unless data has been put in there. can there be a reason why this is like this?

thanks

[foxguy]

Hi t0m46;

Thanks that code works really well. I dont no if a specific sheet is making it slow as the sheet i want to paste the data in is really slow even if i select a cell. the worksheet is empty unless data has been put in there. can there be a reason why this is like this?

I don't believe it makes much difference it there is data in the workbook or not. It just takes time to "Select" a cell (so don't ever "Select" in a macro, just tel that macro what cell to change). If you were inserting or deleting rows, then it would matter if there was data below the inserted/deleted row.

If you want to find out which worksheet is slowing it down try this:

Please Login or Register  to view this content.

Make sure the StatusBar is on. If necessary put this in your code above the loop.

Please Login or Register  to view this content.

and obviously turn it off after the loop if you want.

If the statusbar changes too fast to read the file names, then it's probably as fast as it can be. If it slows down on a particular file, then you have somewhere to start looking for a problem.

[t0m46]

QUOTE=foxguy;2343540] "
If you want to find out which worksheet is slowing it down try this:

Please Login or Register  to view this content.

Hey that works well thanks,

I found that the problem is when it pastes the copied cells into the main workbook. is there a way to speed this bit up as oviously it lookes for the last cell and pastes it there. can it be done another way so it doesnt overwrite the current cells?

thanks

[foxguy]

Hi t0m46;

If it's slow pasting and you're only pasting values, and you have EnableEvents=False, and you have ScreenUpdating=False, then I haven't a clue what's slowing it down.

How did you determine that it was the PasteSpecial that was slowing it down. If I was doing it, I would step through each line 1 at a time. Then I would know for sure which line was slow.
I'm just wondering, you have the time sheet closing right after the PasteSpecial and the next thing is to open another one. That combination might be slow, and if you were only using StatusBar= to find the slowdown you might have missed it.