Can I the find last used row from a closed workbook?

[MPW]

I have over 1,100 old workbooks with macros. They are all built from a template, but vary in how many rows they contain.

In essence, I would like to duplicate the worksheet data (without the VBA code gumming up the works). Turning them into simple workbooks with no vba.
I tried doing this by looping/opening each workbook, but it is extremely slow and some of the vba has been corrupted so they only open manually.

I think I can use "ExecuteExcel4Macro" to accomplish this, even though I have never used it before, but I need to reset the range for each workbook.

So, without opening each workbook
1. I would like to find the last used row
2. Create and duplicate the values on the worksheet.
3. Any tips on using ExecuteExcel4Macro would also be welcomed. I know that this was used when the world was young, but unless there is a better method I will go with it.

Thanks

 

[MPW]

Thank you once again Yongle. This worked really well once I got my brain out of the way
Just had to remove some of the unneeded items and move a few columns around.

This is by far easier than anything I might have come up with. Thanks

 

[Yongle]

Glad that the suggestions proved helpful ?