Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 10-07-2011
Member
 
Join Date: Jun 2005
Posts: 65
How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?
  

I am trying to use a VBA to copy data from the closed workbook to the open workbook on microsoft excel 2010? When the user will open macro-enabled Excel file then there should be an imediate prompt displaying for the user to enter or select file path of the desired workbooks. After that they have to choose 2 files and the file names might not be consistent? Can anyone tell me how should I do it? Thanks

Reply With Quote
  #2  
Old 10-07-2011
Member
 
Join Date: May 2009
Posts: 523
Re: How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?

You can try to use the basic code that is given below. The below code will usually ask the user to choose 2 files and then import the relevant sheet into the current network. Just take a look at the below code:

Code:
Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2

    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub

    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select Second file")
    If Ret2 = False Then Exit Sub

    Set wb2 = Workbooks.Open(Ret1)
    wb2.Sheets(1).Copy Before:=wb1.Sheets(1)
    ActiveSheet.Name = "Blah Blah 1"
    wb2.Close SaveChanges:=False

    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).Copy After:=wb1.Sheets(1)
    ActiveSheet.Name = "Blah Blah 2"
    wb2.Close SaveChanges:=False

    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub
Reply With Quote
  #3  
Old 10-07-2011
Member
 
Join Date: Apr 2009
Posts: 484
Re: How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?

The VBA does not include any method to retrieve the value from the closed file. However you can use the benefit of the Excel so that you can link the files. I recommend that you should simply use the GetValue function to retrieve the value from the closed workbook. It exactly happens when you are supposed to do the calling by the XLM macro.
Reply With Quote
  #4  
Old 11-07-2011
Member
 
Join Date: Apr 2009
Posts: 567
You can also check the below code which is yet another option that you can use:

Code:
Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2

    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub

    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select Second file")
    If Ret2 = False Then Exit Sub

    Set wb2 = Workbooks.Open(Ret1)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(1).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(2).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?"
Thread Thread Starter Forum Replies Last Post
How to copy colors from another workbook to new in Microsoft Excel Talisha MS Office Support 2 24-01-2012 05:08 PM
How to Open up excel Workbook Mahendra varma Software Development 2 30-06-2009 08:18 PM
Copy data from one workbook to another Zool Windows Software 3 16-05-2009 09:42 PM
How to enter Data in Shared Excel Workbook Kamran Windows Software 3 30-04-2009 11:44 AM


All times are GMT +5.5. The time now is 07:31 PM.