Excel 2007 : Button to transfer data from closed excel file to current active excel file

**amandeepsharma89** · 07-13-2012, 12:14 PM

Hello Seniors,
I am need of urgent help. This may be easy but I am not getting how to do it. I have a problem as follows:

I have two excel file A and B. Now, I open excel file A where I have a command button. When I press the command button in file A (active sheet) it should open file B (which is initially closed and in hard drive in some location) copy "column A" of file B and transfer data to "column A" of file A. After copying, file B should close.

Please need urgent macro/vba for it.

Waiting for expert replies.

Thanks!

Amandeep

**JBeaucaire** · 07-13-2012, 06:33 PM

Please Login or Register  to view this content.

**amandeepsharma89** · 07-16-2012, 02:02 AM

Hey JB,
Thanks again for the reply. It works really well. I have two more concerns

1) I want to open particular sheet of that workbook e.g. sheet 2 of Workbook B is that possible?

2) In cell A1 (sheet 2) I have a date field in (transfer of data was performed on sheet 1 of workbook A) which is in format 07/15/2012. Now the workbook B has a name convention as MyFile071512 and it changes everyday tomorrow it will be MyFile071612. So accordingly the user will enter the date in sheet 2 cell A1 of workbook A. And if the user enters 07/12/2012 it the macro should open MyFile071212 if he/she enters 07/15/2012 it should transfer data from MyFile071512.

It will be awesome if I am able to solve this.

Waiting for your expert help!

Thanks!!

Amandeep

**JBeaucaire** · 07-16-2012, 02:22 AM

Please Login or Register  to view this content.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

**amandeepsharma89** · 07-16-2012, 11:55 AM

Hi JB,
Thanks a lot! It works as I want it to be. I have one request, instead of only copying only column A I want to copy the entire sheet from file B to file A, I believe there is only minor tweak in the below code

Please Login or Register  to view this content.

Also, I have a question, everyday when I copy the entire sheet, it will overwrite, right? Because everyday I press the button I need to overwrite the data as the previous day data will be of no use. (replace the entire sheet again with the new sheet)

Waiting for your reply!

Amandeep

**JBeaucaire** · 07-16-2012, 12:38 PM

Please Login or Register  to view this content.

**amandeepsharma89** · 07-16-2012, 02:12 PM

Hi JB,
The code is working fine on my dummy data. But when I am trying to access it file on my shared hard drive it is giving me:

Runtime error '1004'
method open of object workbooks failed

On the line:
Set wbData = Workbooks.Open(fNAME)

Can you let me know what the problem could be?
Amandeep