Results 1 to 4 of 4

Thread: VBA: Copy data from closed Workbook and paste
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Copy data from closed Workbook and paste

    Hi

    I am using this code to copy data from a closed workbook:

    Code:
    Sub GetDataFromClosedBook()
        Dim mydata As String
        
        'data location & range to copy
        mydata = "='F:\filepath\filename.xlsm'!NamedRange" '<< change as required
    
    
        'link to worksheet
        With ThisWorkbook.Worksheets(1).Range("B10:M23")
            .Value = mydata
            'convert formula to text
            .Value = .Value
        
        End With
    End Sub
    It works fine as long as the line:

    Code:
    With ThisWorkbook.Worksheets(1).Range("B10:M23")
    is equal in size as the size of the named range in mydata AND it is at the same spot as the original named range.

    How can I put mydata in other ranges and adapt the
    Code:
    Range("B10:M23")
    to the size of the original named range?

    I hope I am clear, english isn't my mother tongue. Ask away if necessary. Thanks!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,880
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA: Copy data from closed Workbook and paste

    The following macro opens the source workbook, and then copies the values from the named range to the destination workbook, starting at B2. Change the starting cell as desired. Then, once the values have been copied, it closes the workbook.

    Note, however, ScreenUpdating is set to False prior to opening the workbook. So the workbook will be opened in the background and won't be visible to the user. ScreenUpdating is then set to False at the end of the macro.

    Code:
    Option Explicit
    
    Sub GetDataFromClosedBook()
    
    
        Dim pathToFile As String
        pathToFile = "F:\filepath\"
        If Right(pathToFile, 1) <> "\" Then
            pathToFile = pathToFile & "\"
        End If
    
    
        Dim fileName As String
        fileName = "filename.xlsm"
        
        Dim rangeName As String
        rangeName = "NamedRange"
        
        Application.ScreenUpdating = False
        
        Dim sourceWorkbook As Workbook
        Set sourceWorkbook = Workbooks.Open(fileName:=pathToFile & fileName, ReadOnly:=True)
        
        Dim sourceRange As Range
        Set sourceRange = sourceWorkbook.Names(rangeName).RefersToRange
        
        With sourceRange
            ThisWorkbook.Worksheets(1).Range("B2").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        
        sourceWorkbook.Close SaveChanges:=False
        
        Application.ScreenUpdating = True
    
    
    End Sub
    Hope this helps!

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy data from closed Workbook and paste

    Hi

    Thanks for your answer. I'd rather don't want to open the workbooks as that would slow down my application.

    In the end, I will use this to copy data from several workbooks in a loop, so speed has to be kept up.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,880
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA: Copy data from closed Workbook and paste

    Have a look at the following link...

    https://www.rondebruin.nl/win/s3/win024.htm

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •