Results 1 to 4 of 4

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

  1. #1
    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

  2. #2
    Join Date
    May 2009
    Posts
    527

    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

  3. #3
    Join Date
    Apr 2009
    Posts
    488

    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.

  4. #4
    Join Date
    Apr 2009
    Posts
    569
    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

Similar Threads

  1. Replies: 2
    Last Post: 24-01-2012, 05:08 PM
  2. How to Open up excel Workbook
    By Mahendra varma in forum Software Development
    Replies: 2
    Last Post: 30-06-2009, 08:18 PM
  3. Copy data from one workbook to another
    By Zool in forum Windows Software
    Replies: 3
    Last Post: 16-05-2009, 09:42 PM
  4. How to enter Data in Shared Excel Workbook
    By Kamran in forum Windows Software
    Replies: 3
    Last Post: 30-04-2009, 11:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,618,842.13861 seconds with 17 queries