Code to grab values from a closed workbook

[wut]

This is supposedly working code, but it doesn't work for me, and I don't understand how it's supposed to work. Can anyone help with that?

    GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
            "Sheet1", "A1:K30"
End Sub
 
Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
    With ActiveSheet.Range(cellRange)
        .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                    & sName & "'!" & cellRange
        .Value = .Value
    End With
End Sub

Edit: What I mean when I say I don't understand it is that all the variables are never defined. They're never linked to the function's arguments, so how can the code recognize where it's supposed to be looking to fill "fPath", "fName", "sName" and "cellRange"...?

A lot of the tips I'm finding to grab values from a closed workbook are like this. Nothing to set the variables, I don't get how that can work.

 

[alan.m]

This is supposedly working code, but it doesn't work for me, and I don't understand how it's supposed to work. Can anyone help with that?

    GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
            "Sheet1", "A1:K30"
End Sub
 
Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
    With ActiveSheet.Range(cellRange)
        .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                    & sName & "'!" & cellRange
        .Value = .Value
    End With
End Sub

Edit: What I mean when I say I don't understand it is that all the variables are never defined. They're never linked to the function's arguments, so how can the code recognize where it's supposed to be looking to fill "fPath", "fName", "sName" and "cellRange"...?

A lot of the tips I'm finding to grab values from a closed workbook are like this. Nothing to set the variables, I don't get how that can work.

Hi wut,
You have partially quoted the first macro, and that's the key.
The first macro, Test, provides the values
The second macro, GetValuesFromAClosedWorkbook, is called by the Test, and Test passes the values of fPath As String, fName As String, sName, cellRange As String to GetValuesFromAClosedWorkbook.
the full code to use is

Sub test()
    GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
            "Sheet1", "A1:K30"
End Sub
Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As String, sName, cellRange As String)
    With ActiveSheet.Range(cellRange)
        .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                    & sName & "'!" & cellRange
        .Value = .Value
    End With
End Sub

I tried it and it does work, but you'll have to have a Book1.xls in C:\ and have data in Sheet1 range A1:K30
Hope that helps

 

[hiker95]

wut,

What is the directory/folder/fPath to the closed workbook?

What is the filename/fName of the closed workbook?

What is the sheetname/sName of the closed workbook, worksheet containing the range of data values to grad?

What is the range/cellRange of the closed workbook?

The cellRange in the closed workbook (in this case) must match the cellRange in the active/opend worksheet to copy into.




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:K30"
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range(cellRange)
  .FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & cellRange
  .Value = .Value
End With
End Sub

Then run the test macro.

 

[jeffreybrown]

Posted merely as an alternative or in addition.

http://www.rondebruin.nl/ado.htm