This will really depend on how many figures you have linked and by th
sounsd of it theres alot i like to use the macro way to update m
files. the reason for this is i dont have huge formulaes in each cel
etc There is a page on tis web sit
http://tinyurl.com/j5ldg that yo
can look at and there are several different ways to do it wether it
hiding the "other" workbook from opening and closing to updat value
etc - you can check them out
all you have to do is write down your own ranges that you would like t
be crossed over.
eg.
Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an ope
workbook
Dim tArray As Variant
tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls"
"A1:B21")
FillListBox Me.ListBox1, tArray
Erase tArray
End Sub
Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray A
Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
With lb
.Clear
For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
.AddItem
For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray
1)
.List(r, c) = RecordSetArray(c, r)
Next c
Next r
.ListIndex = -1 ' no item selected
End With
End Sub
Private Function ReadDataFromWorkbook(SourceFile As String, _
SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
' this function can only return data from the first worksheet i
SourceFile
' if SourceRange is a defined name reference:
' this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'YOU CAN ADD AS MANY LINES AS YOU WANT HERE
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls"
"DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
dbConnectionString = _
"DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ="
SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the databas
connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rs.GetRows
' returns a two dim array with all records in rs
dbConnection.Close ' close the database connection
rs.Close
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Function
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
Set rs = Nothing
Set dbConnection = Nothing
End Function
hope this helps
cheers
