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

        StartTimer = Timer
        Do While Timer < StartTimer + 2
        '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.