Daily updated information placed in cells by month

Re: Daily updated information placed in cells by month

hey - can you attach your file - kinda difficult to follow what it is your trying to do ...

sounds feasible enough...

also in terms of issue #1 you could use the ontime method but my preference is just to use Windows built in scheduler to launch specific file - then just put a trigger in the workbook_open event to fire your routines as required before closing the file and killing the app.

Re: Daily updated information placed in cells by month

nope - can't be > 50 KBs .. ie must be small .. dumbed down version required perhaps?

Re: Daily updated information placed in cells by month

ok I think i can see what you;re trying to do now but have one question...

the sheet 12 month average - is that for just one Region (from Sheet1)

else I can't work out where the number being added for each day comes from.

as a pointer....

I think you might find it easier to store the data in almost database format...

that is 12 Month Average Sheet as follows:

Column A: Date
Column B: Weekday
Column C: Month (if nec.)
Column D: Number

Then run a pivot table off this data to give you the nice layout you want - with average of course for each month.

Regardless of the pivot - doing the above layout will make your life much simpler in the long run - ie if you had to you could scrap the idea of Pivot and just have another sheet which showed you the average by weekday for each month.

Let us know your thoughts... and also explain the numbers to us on 12 month avg sheet ... that's the only bit I can't follow.

Re: Daily updated information placed in cells by month

ZMERC - I am not being critical but trying to do what you want with the layout you have on sheet "12 Month Average" is really not great... it is possible to find the appropriate cell but it's certainly not as straightforward as it should be.

I think you need to really decide if you can amend the format of the 12 month average sheet so it is laid out in a logical fashion...

I have attached an ex. of what I mean.

I've taken the liberty of putting in a very simplistic piece of code you could use to update your 12 month data ... if you're updating the webdata remotely then you could put this in the sheet event for the datarefresh but we'd need to see the code you're using - or a snippet thereof.

Re: Daily updated information placed in cells by month

no there's no formula - you would have to via VBA ... if you used formulas it would be impossible for you to maintain the prior day's data (and all data prior to today) ... the only way to do what you want is to copy the value from Sheet1 to the appropriate cell in the "Suggested Layout" sheet.

I have inserted a macro contained in Module 1 ... here is the code...

Sub update_daily()
Dim rw As Integer
rw = Application.WorksheetFunction.Match(Sheets("Sheet1").Cells(6, 22), Sheets("Suggested_Layout").Range("A:A"), 0)
Sheets("Suggested_Layout").Cells(rw, 4) = Sheets("Sheet1").Cells(44, 21)
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End Sub

if you were to automate this file in full - ie open, get webdata, update 12 month data, update report etc... you would run the above via a change event - ie once the web data had been downloaded.

I use a similar type event for a currency_matrix I populate daily... i may post that code up to so you can get the gist for how that kind of thing is done (by me anyways!!)

eg....

first a workbook_open event to fire off when the workbook is opened ... and to retrieve data from a website (via a stored webquery within the file).... this file is opened via a Windows Schedule event... and run via an automated network account whereusername = "FD2"... so when opened by the automated account it fires off the routine "DAILY_DATA" and then closes the file... if opened by anyone else it just present with a message.

Private Sub Workbook_Open()
cuser = Environ("username")
If UCase(cuser) = "FD2" Then
    DAILY_DATA
    ActiveWorkbook.Close SaveChanges = False
Else
    MsgBox "This File is Automatically Scheduled to Run at 10am and 1pm on " & _
            "TOPCAT. " & vbLf & vbLf & _
            "Please ensure you do not have this file open at these times!", vbInformation, "Auto Schedule"
End If
End Sub

so presuming run by the FD2 account (ie is being automated) it then runs this code to actually update the data...

Sub DAILY_DATA()


Dim md As Date
QE = 0


'Clear Old Data
Sheets("DAILY").Select
Range(Cells(1, 1), Cells(65536, 2)).ClearContents
Cells(1, 1).Select


'Put in Error Handler - if query won't refresh then continue looping until it does!
10
QE = 0
On Error GoTo QueryError:
Selection.QueryTable.Refresh BackgroundQuery:=False
If QE > 0 Then GoTo 10


Do Until Cells(54, 2) <> ""
    Cells(1, 1).Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
Loop


'LOAD MATRIX INFO TO DB
Dim MyConn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.Open "MYSQL_EXP"


'Remove existing data for today and tomorrow
MySQL = "Delete From currency_matrix where date >= '" & _
        Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "'"
Set MyRS = MyConn.Execute(MySQL)


n = 2
Do Until Cells(n, 5) = ""
On Error GoTo Problemo:
    'Insert today
    MySQL = "Insert into currency_matrix values (" & _
            "'" & Cells(n, 3) & "', " & _
            "'" & Cells(n, 4) & "', " & _
            "'" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "', " & _
            Cells(n, 5) & ", " & _
            Cells(n, 6) & ", " & _
            Cells(n, 7) & ", " & _
            Cells(n, 8) & ", " & _
            Cells(n, 9) & ", " & _
            Cells(n, 10) & ", " & _
            Cells(n, 11) & ", " & _
            Cells(n, 12) & ", " & _
            Cells(n, 13) & ", " & _
            Cells(n, 14) & ", " & _
            Cells(n, 15) & ", " & _
            Cells(n, 16) & ", " & _
            Cells(n, 17) & ", " & _
            Cells(n, 18) & ", " & _
            Cells(n, 19) & ", " & _
            Cells(n, 20) & ", " & _
            Cells(n, 21) & ")"
    Cells(n, 22) = MySQL
    Set MyRS = MyConn.Execute(MySQL)
    
    md = DateValue(Now + 1)
    'Insert tomorrow
    MySQL = "Insert into currency_matrix values (" & _
            "'" & Cells(n, 3) & "', " & _
            "'" & Cells(n, 4) & "', " & _
            "'" & Year(md) & "-" & Month(md) & "-" & Day(md) & "', " & _
            Cells(n, 5) & ", " & _
            Cells(n, 6) & ", " & _
            Cells(n, 7) & ", " & _
            Cells(n, 8) & ", " & _
            Cells(n, 9) & ", " & _
            Cells(n, 10) & ", " & _
            Cells(n, 11) & ", " & _
            Cells(n, 12) & ", " & _
            Cells(n, 13) & ", " & _
            Cells(n, 14) & ", " & _
            Cells(n, 15) & ", " & _
            Cells(n, 16) & ", " & _
            Cells(n, 17) & ", " & _
            Cells(n, 18) & ", " & _
            Cells(n, 19) & ", " & _
            Cells(n, 20) & ", " & _
            Cells(n, 21) & ")"
            Cells(n, 22) = MySQL
    Set MyRS = MyConn.Execute(MySQL)
        
n = n + 1
Loop


Exit Sub


QueryError:
QE = 1
Resume Next


Problemo:
Cells(n, 23) = Err.Description
Resume Next


End Sub

here it loops until it has successfully retrieved the required data from the web query ....

the rest you can ignore (its just loading data from various cells to a MySQL db for the current day & next)

in your case you'd simply replace all that bumpf with the code that copies the cell you want to the next blank cell... (as per the first bit of code I put up)

anyway - that's the general approach....

for others reading this... I wrote the code a long time ago so undoubtedly there are tweaks... but as it takes less than about 5 seconds to run in full I am not that fussed