Copying data from a closed workbook with macro

I found the below code online for copying data from a closed workbook to my currently open file, couple of issues that I would like to get help with.
•Data is copied okay but when pasting into A1:D6 it pastes the same data in every cell
•I need it to copy/paste exactly from the original data, could be numbers, text, or general formatted data.


Thanks

Please Login or Register  to view this content.

Thank you.
I tried your suggestion and the macro filled in all the cell with "#VALUE"

Any thoughts?

6StringJazzer, thanks for your help.
It did copy the data but in my dataset there are some empty cells, when it pasted the data it filled in the empty cells with 0's.
If the cell is empty I need it to be empty if the original cell has a 0 then I need it to show a 0.
Please help. Thanks

It looks to me like the call to ExecuteExcel4Macro returns a 0 if the cell is blank. I don't think there is any way to detect that; I can't find a detailed specification for how this works. You could rewrite the code to open the file, copy, then close it (any reason that it has to remain closed?), or here is another approach using ADO that also allows the file to remain closed.