Opinions on external query

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
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.



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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,176,614
Messages
5,904,023
Members
435,068
Latest member
StacyJ01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top