DB Query refresh/Update macro

[lionelnz]

Hi everybody.

I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.

Then runs a query on an Access DB to place in the above worksheet.

I need this due to novice XL & DB (8)users.

This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files -

Sub TestDBQuery()
'
' TestDBQuery Macro
' Macro recorded 16/06/2008 by New Owner
'

'
Dim rng As Range
If ActiveSheet.Name <> "Tec" & Format(Date, "YYMMDD") Then
    ActiveSheet.Name = "Tec" & Format(Date, "YYMMDD")
    ActiveSheet.Range("A1").CurrentRegion.Delete
    Else
    End If


    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\New Owner\My Documents\01VBA VB\budget.mdb;DefaultDir=C:\Documents and Set" _
        ), Array( _
        "tings\New Owner\My Documents\01VBA VB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT budget.SORT, budget.DIVISION, budget.CATEGORY, budget.ITEM, budget.MONTH, budget.BUDGET" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\New Owner\My Documents\01VBA VB\budget`.budget budget" & Chr(13) & "" & Chr(10) & "WHERE (budget.MON" _
        , _
        "TH='Jan') AND (budget.DIVISION='N. America') OR (budget.MONTH='Feb') AND (budget.DIVISION='N. America') OR (budget.MONTH='Mar') AND (budget.DIVISION='N. America')" & Chr(13) & "" & Chr(10) & "ORDER BY budget.CATEGORY" _
        )
        .Name = "Query from MS Access Database2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
    
End Sub

One of the problems I had is that if I ran the macro on the activesheet where the query had already placed results it would create "A1" at the next available empty cell on Row1. I tried to hard code A1 as Range via "Dim rng As Range" & with "rng = activesheet.Range("A1")" & place rng in the first part of the array for the destination. Did not work.

Next problem I have is that I only want run the macro only once on the very first workbook open for the day but then the data is replaced the next day in the same sheet of the same workbook but the sheet is renamed to the day's date.

Any advice or help is much appreciated.

Cheers

 

[Andrew Poulsom]

Have you tried?

ActiveSheet.QueryTables(1).Delete

 

[lionelnz]

Thanks for quick response.
I have now & this did not work.

 

[Richard Schollar]

How did it not work? Please use as much description as possible.

 

[lionelnz]

Didn't delete the old data. Just added a 2nd set of query data starting from the 1st empty cell in Row1.

BTW - I am using XL 2002 prof at home now & at work use XL 2003 Prof.

 

[Richard Schollar]

Deleting the querytable doesn't delete the worksheet data - it instead removes the mechanism behind the worksheet data that refreshes that data. If you wish to remove the data fully, you should use Excel tools (like Clear and ClearContents) and remove the QT to prevent it being refreshed and thus recreated.

 

[lionelnz]

So I should use this

"ActiveSheet.Range("A1").CurrentRegion.ClearContents"

instead of .Delete?

 

[Richard Schollar]

That will ceratinly get rid of the CurrentRegion's data (not formatting). If you also want to prevent users from refreshing the data, then you should delete the actual QueryTable too.

There are some settings with QTs that I always apply - one is to have:

Activesheet.QueryTables(1).RefreshStyle = xlOverwriteCells

I find it prevents a lot of headaches.

 

[lionelnz]

Thanks as this is one of the headaches I do have. Wanting to refresh the table on Open for the 1st time for the day.

 

[Richard Schollar]

If you want to only run it the first time in any day that is it opened, then you need to store a flag or value somewhere (preferably on the worksheet or in a name) that you can refer to each time it is opened. Eg say cell A1 on sheet1. Each time the worksheet opens, have the Workbook_Open event check what the value is in Sheet1!A1 - if it already carries today's date, then exit sub and don't refresh. If the date is something other than today's date, refresh the QT and then write today's date into the cell, so that subsequent opening won't trigger the refresh again. make sense?