Get Values from other workbook without opening

Vishesh's picture

You can use the following piece of code to get values into an opened workbook from a closed workbook.

 

Sub GetRange(strSrcFilePath As String, strSrcFileName As String, strSrcShtName As String, _
 
             strSrcRange As String, rngTarget As Range)
 
    Dim StartTimer
 
 
 
    'Resize target range 'rngTarget' to the same size as source range 'strSrcRange'

    With Range(strSrcRange)
 
        Set rngTarget = rngTarget.Resize(.Rows.Count, .Columns.Count)
 
    End With
 
 
 
    'Add formula links referring to the closed file

    With rngTarget
 
        .FormulaArray = "='" & strSrcFilePath & "/[" & strSrcFileName & "]" & strSrcShtName & "'!" & strSrcRange
 
 
 
        'Wait

        StartTimer = Timer
 
        Do While Timer < StartTimer + 2
 
            DoEvents
 
        Loop
 
 
 
        'Make values from formulae

        .Value = .Value
 
    End With
 
End Sub

Get Values from other workbook without opening

Would you perhaps have an example workbook demonstrating this. I am having difficulty getting this to run. I am a novice.