Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks - Move file to local drive, all links break | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
Prompt to update links | Links and Linking in Excel | |||
Deleting links to other spreadsheets | Excel Worksheet Functions | |||
can't update links...can't find links | Excel Discussion (Misc queries) |