VBA: Copy data from closed Workbook and paste

cozzagiorgi

New Member
Joined
Jun 27, 2018
Messages
41
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!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,046
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!
 

cozzagiorgi

New Member
Joined
Jun 27, 2018
Messages
41
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.
 

Forum statistics

Threads
1,086,259
Messages
5,388,728
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top