ioncila
05-18-2010, 11:33 AM
Hi
This is a small but annoying issue:
I have this proccedure that copies a range of data from a closed book without opening
(The below in a module)
Sub actualize() 'Update data from a closed book
Dim fPath As String
Dim fName As String
Dim sName As String
Dim cellRange As String
'Details of the closed book
fPath = "\\Server\SG\SGmatrix\SGorc\"
fName = "SGregEquip.xls"
sName = "INDICE" 'worksheet
cellRange = "E11:E500" 'range
GetValuesFromAClosedWorkbook fPath, fName, _
sName, cellRange
End Sub
Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
'cellRange copies range to the same range in destination book. For another range must use OFFSET
With Worksheets("Calc EQUIP").Range(cellRange).Offset(0, -4)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
The bellow in Workbook Open
Private Sub Workbook_Open()
Run "actualize"
End Sub
This works very fine but, for that, I need to open a new or another workbook. If I only open my workbook, it generates an error #REF.
How do I pass over this issue?
Thank you
This is a small but annoying issue:
I have this proccedure that copies a range of data from a closed book without opening
(The below in a module)
Sub actualize() 'Update data from a closed book
Dim fPath As String
Dim fName As String
Dim sName As String
Dim cellRange As String
'Details of the closed book
fPath = "\\Server\SG\SGmatrix\SGorc\"
fName = "SGregEquip.xls"
sName = "INDICE" 'worksheet
cellRange = "E11:E500" 'range
GetValuesFromAClosedWorkbook fPath, fName, _
sName, cellRange
End Sub
Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
'cellRange copies range to the same range in destination book. For another range must use OFFSET
With Worksheets("Calc EQUIP").Range(cellRange).Offset(0, -4)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
The bellow in Workbook Open
Private Sub Workbook_Open()
Run "actualize"
End Sub
This works very fine but, for that, I need to open a new or another workbook. If I only open my workbook, it generates an error #REF.
How do I pass over this issue?
Thank you