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

Option Explicit

Sub ImportData()
Dim ws1 As Worksheet, wbData As Workbook

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wbData = Workbooks.Open("C:\Path\To\My\File\MyFile.xls")

ActiveSheet.Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbData.Close False

Application.ScreenUpdating = True
End Sub

**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

Option Explicit

Sub ImportData()
Dim ws1 As Worksheet, wbData As Workbook
Dim fNAME As String

Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Sheets("Sheet2")
fNAME = "C:\Path\To\My\File\MyFile" & Format(CDate(ws1.Range("A1")), "MMDDYY") & ".xls"

If Len(Dir(fNAME)) = 0 Then
    MsgBox "That dated file '" & fNAME & "' does not exist."
    Exit Sub
End If

Set wbData = Workbooks.Open(fNAME)

wbData.Sheets("Sheet2").Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbData.Close False

Application.ScreenUpdating = True
End Sub

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

wbData.Sheets("Sheet2").Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbData.Close False

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

Option Explicit

Sub ImportData()
Dim ws1 As Worksheet, wbData As Workbook
Dim fNAME As String

Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Sheets("Sheet2")
fNAME = "C:\Path\To\My\File\MyFile" & Format(CDate(ws1.Range("A1")), "MMDDYY") & ".xls"

If Len(Dir(fNAME)) = 0 Then
    MsgBox "That dated file '" & fNAME & "' does not exist."
    Exit Sub
End If

Set wbData = Workbooks.Open(fNAME)

On Error Resume Next
ws1.UsedRange.Clear
wbData.Sheets("Sheet2").UsedRange.Copy ws1.Range("A1")
wbData.Close False

Application.ScreenUpdating = True
End Sub

**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