Updating a Master workbook from multiple workbooks (Excel 2000)

[rpudney]

Hi all,

I would like to know if there is a way in Excel 2000 (Sadly our organisation is rather behind on the times) to essentially do the following:

Books A,B & C are seperate workbooks that are all formatted in the same way (same number of columns and headings/titles,etc). The data in these workbooks has a seperate date and timestamp for each row of data but all three workbooks contain data where the date/time stamp is only 1 or 2 seconds. The dates and times for this data cover the same period and will continue to do so as they get updated over the next few months.

What I would like to do is to is create a Master workbook that collates the data from all three workbooks, sorts them in an ascending date, then time order and is capable of automatically updating itself with new data when new data is added to one or more of the seperate workbooks.

Having seperate workbooks is my preferred option however I have no issues with creating one workbook with multiple worksheets in it if there is an easier and faster way to do it that way.

Thank you in advance for your time,

RG

 

[BrianB]

have a look at my code here

http://www.mrexcel.com/forum/showthread.php?t=140187

 

[conny2254]

Im not sure if this is working in excel 2000 but the following code loops through a folder and imports the data from the range B11:S29 (row by row). To use this I would put your master workbook in a folder then put another folder with your other 3 workbooks as a sub folder in the folder and that would be the name of the folder in red below. Another alternative to this is to actually open each file and copy and paste the data between workbooks, which is slower but will not be too slow if your only importing data from 3 workbooks at a time, but the benefit of opening the workbook and copy/pasting is you can copy and paste info based on conditions you set, where as importing with a formula you cannot. Ron de Bruin has a great website covering this. Hope this helps.

http://www.rondebruin.nl/copy3.htm

Sub Import()
    Dim MyPath As String, Location As String, FilesInPath As String
    Dim Myfolder As String, MyFiles() As String
    Dim Fnum As Long, CalcMode As Long, k As Long
    Dim MainDBwb As Workbook
            Location = ThisWorkbook.Path
            Myfolder = "\YOURFOLDERSNAME\"
 
            'Fill in the path\folder where Flight Logs are
            MyPath = Location & Myfolder 
 
            'If there are no Excel files in the folder exit the sub
            FilesInPath = Dir(MyPath & "*.xls*")
            If FilesInPath = "" Then
                Exit Sub
            End If
 
            'Fill the array(myFiles)with the list of Excel files in the folder
            Fnum = 0
            Do While FilesInPath <> ""
                Fnum = Fnum + 1
                ReDim Preserve MyFiles(1 To Fnum)
                MyFiles(Fnum) = FilesInPath
                FilesInPath = Dir()
            Loop
 
            'Loop through all files in the array(myFiles)
            If Fnum > 0 Then
                For Fnum = LBound(MyFiles) To UBound(MyFiles)               
                    For k = 11 To 29 Step 2
                        Set MyRange = Range(ThisWorkbook.Sheets(1).Range("C10000").End(xlUp).Offset(1, 0), ThisWorkbook.Sheets(1).Range("C10000").End(xlUp).Offset(1, 17))
                        MyRange = "=if('" & MyPath & "[" & MyFiles(Fnum) & "]" & YOURSHEETNAME & "'!B" & k & "="""",""""," & "'" & MyPath & "[" & MyFiles(Fnum) & "]" & YOURSHEETNAME & "'!B" & k & ")"
                        MyRange.Value = MyRange.Value
                    Next k
 
                Next Fnum
            End If