Reference a Range in a closed workbook in VBA

[axg275]

Hello,

Do you guys know how I can reference range in a closed workbook on my network drive?

Thanks

Alex

 

[Fazza]

Hi, Alex. In what context? Fazza

 

[BrianB]

This might be what you are looking for

'=============================================================================
'- FUNCTION TO GET VALUES FROM A CLOSED WORKBOOK
'- USES ExecuteExcel4Macro
'- Brian Baulsom May 2005
'=============================================================================
Dim MyPath, MyFile, MySheet, MyCell     ' worksheet
Dim R, C                                ' row, column
'=============================================================================
'- MACRO 1 : SINGLE CELL
'=============================================================================
Sub TestGetValue1()
    MyPath = "F:\"               ' path
    MyFile = "TestData2.xls"     ' file
    MySheet = "Sheet1"           ' sheet
    MyCell = "A1"                ' range
    '------------------------------------------------------------------------
    MsgBox GetValue(MyPath, MyFile, MySheet, MyCell)
    '------------------------------------------------------------------------
End Sub
'------- end of sub 1 -------------------------------------------------------
'=============================================================================
'- MACRO 2 : MULTIPLE CELLS (90 rows, 6 columns)
'=============================================================================
Sub TestGetValue2()
    MyPath = "F:\"               ' path
    MyFile = "TestData2.xls"     ' file
    MySheet = "Sheet1"           ' sheet
    '------------------------------------------------------------------------
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")        '- TARGET WORKSHEET
        '--------------------------------------------------------------------
        '- LOOP CELLS BY ROW & COLUMN
        For R = 1 To 90
            For C = 1 To 6
                MyCell = Cells(R, C).Address
                .Cells(R, C) = GetValue(MyPath, MyFile, MySheet, MyCell)
            Next C
        Next R
        '--------------------------------------------------------------------
    End With
    '------------------------------------------------------------------------
    MsgBox ("Done")
    Application.ScreenUpdating = True
End Sub
'----------- end of sub 2 ----------------------------------------------------
'=============================================================================
'- FUNCTION
'=============================================================================
Private Function GetValue(Fpath, Ffile, Fsheet, Fref)
     Dim XL4macro As String
    '- Excel 4 macro string (requires R1C1 reference)
    XL4macro = "'" & Fpath & "[" & Ffile & "]" & Fsheet & "'!" & _
      Range(Fref).Address(ReferenceStyle:=xlR1C1)
    '-------------------------------------------------------------------------
    '-Run the macro
    GetValue = ExecuteExcel4Macro(XL4macro)
End Function
'-----------------------------------------------------------------------------

 

[Fazza]

Also, if relevant, the method Brian has used can be applied over ranges other than just a single cell. Will be much faster. Depends on what you are wanting.

There are also other ways such as with ADO that can work much more efficiently. Depends on what you are doing.

It is difficult to advise, Alex, without knowing what exactly is wanted. Regards, Fazza

 

[javierc]

This might be what you are looking for

'=============================================================================
'- FUNCTION TO GET VALUES FROM A CLOSED WORKBOOK
'- USES ExecuteExcel4Macro
'- Brian Baulsom May 2005
'=============================================================================
Dim MyPath, MyFile, MySheet, MyCell     ' worksheet
Dim R, C                                ' row, column
'=============================================================================
'- MACRO 1 : SINGLE CELL
'=============================================================================
Sub TestGetValue1()
    MyPath = "F:\"               ' path
    MyFile = "TestData2.xls"     ' file
    MySheet = "Sheet1"           ' sheet
    MyCell = "A1"                ' range
    '------------------------------------------------------------------------
    MsgBox GetValue(MyPath, MyFile, MySheet, MyCell)
    '------------------------------------------------------------------------
End Sub
'------- end of sub 1 -------------------------------------------------------
'=============================================================================
'- MACRO 2 : MULTIPLE CELLS (90 rows, 6 columns)
'=============================================================================
Sub TestGetValue2()
    MyPath = "F:\"               ' path
    MyFile = "TestData2.xls"     ' file
    MySheet = "Sheet1"           ' sheet
    '------------------------------------------------------------------------
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")        '- TARGET WORKSHEET
        '--------------------------------------------------------------------
        '- LOOP CELLS BY ROW & COLUMN
        For R = 1 To 90
            For C = 1 To 6
                MyCell = Cells(R, C).Address
                .Cells(R, C) = GetValue(MyPath, MyFile, MySheet, MyCell)
            Next C
        Next R
        '--------------------------------------------------------------------
    End With
    '------------------------------------------------------------------------
    MsgBox ("Done")
    Application.ScreenUpdating = True
End Sub
'----------- end of sub 2 ----------------------------------------------------
'=============================================================================
'- FUNCTION
'=============================================================================
Private Function GetValue(Fpath, Ffile, Fsheet, Fref)
     Dim XL4macro As String
    '- Excel 4 macro string (requires R1C1 reference)
    XL4macro = "'" & Fpath & "[" & Ffile & "]" & Fsheet & "'!" & _
      Range(Fref).Address(ReferenceStyle:=xlR1C1)
    '-------------------------------------------------------------------------
    '-Run the macro
    GetValue = ExecuteExcel4Macro(XL4macro)
End Function
'-----------------------------------------------------------------------------

Hi, I need code similar to TestGetValue2 but when vba run the function TestGetValue2, vba show error 9 subscript out of range

Does anyone know why and how to correct it?

Regards