Auto Refresh in Excel

[NellyC]

I have an Excel spreadsheet containing a link to an Access Database.

Is there a way to set a macro that will refresh the records from the database based on a time frame (i.e. every 10 minutes)

Thanks

NellyC

 

[RonRepp]

I usually use the menu Data>>Get External Data>>New Database query to bring results into Excel. I have recorded this using a macro so that you can see what happens.

Sub RefreshMyData()
     With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MS Access Database;DBQ=F:\Warehouse.mdb;DefaultDir=F:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT TDWBWS.SCRIPTNAME, TDWBWS.CONNECTSTR, TDWBWS.FILENAME, TDWBWS.OPENSCRIPT, TDWBWS.OPTIONS" & Chr(13) & "" & Chr(10) & "FROM `F:\Warehouse`.TDWBWS TDWBWS" _
        )
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 5
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

The time frame is .RefreshPeriod

I hope this helps.




Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

Another thought:

If you are not using code, click the properties toolbar button (2nd button on the external data toolbar.

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[NellyC]

Excellent, Worked a treat

Thanks

NellyC

 

[RonRepp]

My pleasure.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.