Help Tweaking this Code

[Markus]

The following will return the values on a sheet in a
closed workbook and write them to the same range on the
active worksheet. I want to write them to a specified
range on an inactive worksheet. Instead of the active
sheet I'd like to send it to Worksheet("SR") and Range
("A8")

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Sheet1", "A1"
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

Thanks

 

[Bob Phillips]

Sub test()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Sheet1", "A8"
End Sub

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[NickHK]

Markus,
A small point, but you're not really getting any value from a closed book.
You're inserting an external link in the current book. So something like
this may be more appropriate:

Function InsertLinkToExtWB (argThisWBSheet as String, _
argThisWBCell as String, _
argExtWBPath as String
argExtWBName as string
argExtWBSheet as String, _
argExtWBCell as String) _
As Boolean


ThisWorkbook.WorkSheets(argThisWBSheet).Range(argThisWBCell).Formula=.......
.........


'Use return value to indicate success of finding ExtWB, etc if required

End Function


NickHK