VBA: Copy data from closed Workbook and paste

[cozzagiorgi]

Hi

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

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:

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

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]

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.

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]

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.

 

[Domenic]

Have a look at the following link...

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