Links
 

Hi

I have normally 5-15 files containing cells which give the volumes of
solutions. This data is linked to a master file. I then have a third
file that reads the master file and is used to print out a list of
weights so the actual weights can be compared.

This only works if the files "update" and it does not always happen.

Question: is there a better way to link files (actively) than
=(filename,cell)?

Many thanks

Andrew

Links
 

This will really depend on how many figures you have linked and by the
sounsd of it theres alot i like to use the macro way to update my
files. the reason for this is i dont have huge formulaes in each cell
etc There is a page on tis web sit http://tinyurl.com/j5ldg that you
can look at and there are several different ways to do it wether its
hiding the "other" workbook from opening and closing to updat values
etc - you can check them out

all you have to do is write down your own ranges that you would like to
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 open
workbook
Dim tArray As Variant
tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls",
"A1:B21")
FillListBox Me.ListBox1, tArray
Erase tArray
End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As
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 in
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.x ls", "A1:A21")
'""""""""""""""""""""""""""""""""""""""""""""""""" """""""""""""""""""""""""""""""""""""""""""""" ""
'YOU CAN ADD AS MANY LINES AS YOU WANT HERE
'""""""""""""""""""""""""""""""""""""""""""""""""" """""""""""""""""""""""""""""""""""""""""""""" ""'

' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls", "A1:B21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.x ls",
"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 database
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.


--
Zygan
------------------------------------------------------------------------
Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
View this thread: http://www.excelforum.com/showthread...hreadid=549365