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.
End Sub
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.
VBA Code:
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