Resresh DB import within a macro

I am looking to refresh a DB import within a macro but I am not sure how to get the time of the last refresh. Could someone point me in the right direction.

Also, is there a way to set a macro to run at given intervals or is a worksheet calculation the best way to do it?

Hello Goofy78270,

You don't say if you are using a Query Table to refresh your database, but since you are asking about refresh periods, my assumption is you are. Here is an example...

Worksheets("Sheet1").Range("A10").QueryTable.Refresh

A10 is a cell that intersects with the Query Table. This way you don't have to remember the Query Table index for that area, since you can have more than one Query Table per worksheet.

You can use this same strategy to the refresh time in minutes...

Dim T As Long
  T = Worksheets("Sheet1").Range("A10").QueryTable.RefreshPeriod

Sincerely,
Leith Ross

So will "T" store the time of the last refresh or is the .RefreshPeriod the interval? I am curious as to the time, because most of the information from the DB will not change, so i do not want to set a refresh everytime the macro is ran but at a varying interval from the last refresh. To explain a little more, if I choose to refresh the information every 10 minutes but the user is trying to work with the given information, it will reset all of the values - not good. Once a query is ran, I would like the user to be able to work with the given data without it refreshing, but if the user wants new information, then the macro will evaluate the current time to the last refresh and update if necessary.

Hello Goofy78270,

VBA doesn't provide any time data about the refresh, only the time interval between refresh cycles. You can suspend the automatic refresh by setting the refresh period to 0. You can then restore it back to the original value whenever you like. You can test to see if the Query Table is refreshing by testing the Refreshing property.

If Worksheets("Sheet1").Range("A10").QueryTable.Refreshing = False Then
  'Update/Retrieve data from the Query Table 
Else
  'Notfiy the user or wait until refresh is done
End If

Sincerely,
Leith Ross