Opinions on external query

[rickblunt]

Greetings, I am using the following code in a WB to allow the user to sort a worksheet of data and return the relevant list of data into a combobox on a worksheet. This works fine so there is no problem there.

But now I am creating another workbook (this workbook will be called "Facilities Information Center") that is retrieving data from other workbooks to allow the users to quickly see a collection of different sets of data. All of these workbooks are located on our servers (in this case ("L:\CommonRW\Facilities Data"). Tweaking the code to reference the workbooks is easy enough but I know that I will need to open and close these workbooks in order for the data to refresh. The data is only generated once or twice a day but the queries could happen multiple times a day or even in succession. The current WB currently auto updates every 30 minutes to refresh the data.

So I can think of two ways of doing this. Either run the macro to open and close all of the workbooks in succession every time the user performs a query or actually copy all of the relevant workbook data into the main workbook itself. I am thinking that the query may be quicker (or less clunky?) if I copy all of the data into the main WB instead of going out a getting it every time a query is requested. But copying all of the data over may be more of an issue instead because the amount of data that is going to growing quite a bit over time.

I would appreciate your opinions on how you would do it if it was you that was doing it - I appreciate any input.

Private Sub RepairHistoryButton_Click()

 Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Dashboard")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
        .List(.ListCount - 1, 5) = i
       End With
    End If
  Next

End Sub

 

[EFANYoutube]

Hi rickblunt
My honest opinion is the have your data stored in an access database and when someone add an entry it adds it to the database and if you want data you just do an sql query on the database and it will return everything you want
That way the data is always updated and the queries are quick(unless your company servers are somewhere far away)
you could get the data you want for one query in les than .5 of a second