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 -
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
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 -
Code:
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