Microsoft Office Tutorials and References
In Depth Information
Some Useful Functions for Use in Your Code
‘ Create the argument
arg = “’” & path & “[“ & file & “]” & sheet & “’!” & _
Range(ref).Range(“A1”).Address(, , xlR1C1)
‘ Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
The GetValue function takes four arguments:
h path : The drive and path to the closed file (for example, “d:\files”)
h file : The workbook name (for example, “budget.xlsx”)
h sheet : The worksheet name (for example, “Sheet1”)
h ref : The cell reference (for example, “C4”)
The following Sub procedure demonstrates how to use the GetValue function. It displays the
value in cell A1 in Sheet1 of a file named 2010budget.xlsx , located in the XLFiles\
Budget directory on drive C.
Sub TestGetValue()
Dim p As String, f As String
Dim s As String, a As String
p = “c:\XLFiles\Budget”
f = “2010budget.xlsx”
s = “Sheet1”
a = “A1”
MsgBox GetValue(p, f, s, a)
End Sub
Another example follows. This procedure reads 1,200 values (100 rows and 12 columns) from a
closed file and then places the values into the active worksheet.
Sub TestGetValue2()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long
p = “c:\XLFiles\Budget”
f = “2010Budget.xlsx”
s = “Sheet1”
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
End Sub
Search JabSto ::

Custom Search