• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a history table in my Excel sheet that appends a new line when the data from a MySQL query changes.

adams

New Member
I am trying to create a history table that appends new data lines when specific data from my MySQL query changes. I have 7 values on one line that are being updated from a MySQL database in columns A-G. Header in row one and values in row 2. When Column C changes, I would like the values in row 2 to be appended to the history data in columns A to G starting at line 6 and appending a line below that anytime C2 changes, So it should do it automatically based on when the data in cell C2 changes, not require me to push a button to copy the data down because it needs to do this 24/7 so that we can look at it to see what time the values changed.

I thought the hard part would be getting the data from MySQL into my worksheet, but that turned out to be fairly easy (Considering I just started learning MySQL). I haven't been able to copy the values down automatically as static values in my history table.

Any help would be appreciated.
 

Attachments

  • KlondikeTracker.xlsx
    12.3 KB · Views: 4
Possibly this...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(2, 3)) Is Nothing Then
        Range("A2:G2").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    End If
End Sub
...copied into the worksheet's code module.
 
Thanks RDAngelo,

I tried it and it copies the line from row 2 to row 3 when it updates, then to row 4 when it updates again but then just updates all 3 lines with the same data every time that it updates and all 3 lines have the same data, rather than showing a history. Also it stops appending at 3 lines.

What I need it for the current line to copy down and append as a static value that doesn't retain a link to line 2 so that I have a history of how it has changed. Also need it to keep appending a new line every time. Thousands of lines is ok, if they are not all the same values.

I appreciate the help though, it's way farther than I got.

Could you explain the top 3 lines of code?

Regards,
Dave
 
...Could you explain the top 3 lines of code?

The first line calls the worksheet change event.
The second line checks if the cell C2 was one of the cells that triggered the change event.
The third line copies the range A2:G2 to the next empty line below row 5.

This code works in the attach workbook.
 

Attachments

  • KlondikeTracker (1).xlsm
    17.7 KB · Views: 10
Ok, thanks

I can see it works great in the sheet you sent back with data changed manually. When I populate those cells with a MySQL query however it behaves as I described above. I’ll try to see if has something to do with the query formatting and if I have any control over it.

I can’t upload the actual sheet because it links to my companies database and they wouldn’t like that. I’ll try to find a public domain database that I can get data from and post another example with an actual query from live data to see if I can duplicate my situation.

thanks again for the help!
Dave
 
adams
Do You run any macro to get Your results to row2?
If Yes then 'copy'-code should add there.
or
You could add below two lines to avoid extra copying.
Application.EnableEvents = False
code which copies needed range to history row
Application.EnableEvents = True
 
No, I didn’t have any macros running before trying the one suggested here. My line updates on an interval of 1 minute or more selected in the MySQL Query. I’m going to try running the query in a different sheet of the same workbook and put links to the data from the query in a separate sheet with the macro and see if that works. But I can’t try until Monday.
Thanks
 
It didn't work to run my query in a separate sheet and update the 1st sheet by linking the 2 sheets with formulas either. In an attempt to simulate what is happening when I update with the MySQL query I created a workbook with a seperate sheet to change the values in. Here is an example workbook with 2 worksheets. Input1 sheet to input the values and change them simulating the Mysql database Query, and Update-History sheet that is linked to the cells in Input 1 sheet via formulas and has the Macro suggested above to create a history table in columns A to E when the value in cell C2 changes.

If I change the value of C2 in Input1, cell C2 in Update-History sheet changes but it doesn't append a line. However, if I manually change the value in C2 of the Update-History sheet, it does append a line, but it is just copying down the formulas, so the history is all to same data.

See attached workbook
 

Attachments

  • Test Klondike History manually.xlsm
    23.6 KB · Views: 3
adams
As I tried to ask #6 reply...
How do You get those values to that sheet (original - Sheet1)?
... and now, You show that those are linked from other sheet ( now - from Input1).
Case is different now!
... and that code won't work.
>> How do You get those values now ( newer version) to Input1? <<
Of course, You wrote when I update with the MySQL query
... how do You update that?
 
MySQL plugin for Excel has an auto refresh feature 71707
I just select how often I want it to update from the database, with the minimum being 1 minute. I would probably keep this on 10 minutes update if I could get the history saved. That will allow me to see if my equipment has been moved to a new location overnight and at what time it happened when I get up in the morning and over the weekend.

Thanks for helping
 
adams
These are samples.
a) Could Your used 'MySQL plugin for Excel' also run ... macro?
.. if 'Yes' then something else
.. if 'No' then b)

b) If Your used 'MySQL plugin for Excel' works that way -
- - - it could use Events then Sheet2's code would work ... otherwise clear it away!

c) I added a [ Run/Stop ]-button in 'Update-History'-sheet
> enable Your 'MySQL plugin for Excel' ... that it works in this file
> press that button once ... it should do as You would like to happen ( now, every 10seconds )
> cell G1 shows number of copies
> to 'stop' ... recording ... press that button once
 

Attachments

  • Test Klondike History manually.xlsm
    25.7 KB · Views: 10
I'll keep trying, but no success so for getting it to update history section with my data when it changes from the MySQL Query.
 
adams
Did You use same file (#11 reply)?
Did You test both samples?
... with c) Did cell G1-value changed?
Are macros enabled?
It's a challenge even to guess, what could do? - based Your sentence.
Only that ... trying ... is different than doing something.
 
Sorry, I made quite a few attempts with different methods.
1st I tried the sheet manually and it updated G1 and the history file when I changed the values of C2 directly in the Update-History sheet
Then I tried to update from linked cells (A2 in Update-History sheet =Input1!$A$2) etc. A2 through E2 updated, it did update the history table and the G1 value slowly increases if I am updating or not.
Then I added the SQL query to Input 1 sheet instead of manually entering values and Update_History sheet. It didn't seem to work yesterday, but it might have worked and updated the table. I'll have to wait for the equipment to move to a new location to verify.

I just got a subscript out of range error while waiting for location to change. G1 is showing 528.

71758

Thanks for your assistance.
 
Last edited:
adams
...hmm? What could I write?
If You have gotten instructions --- then --- of course --- You seems not follow those --- okay.
As well as You seems to have there other Excel-file open too
... that could make Your snapshot-like unwanted result.
It would be more than surprise if those would work.
A sample needs to test as written.
If You're using doing something else ... then the results could be something else!
If You've eg an idea to uses other Excel-files same time, then that should write in that code too.
 
adams
Did You those tests?
I tested those ... without Your 'MySQL' ... manually and those worked here.

About Your thread:
Your 'MySQL' works 1minute intervals (later 10 minute) - okay?
option b)
It 'gives' values every 1minute - do it really 'refresh' those Your five values in same time? ... or one-by-one from A-column?
If so ... then option b) works after there has refreshed cell C2-value ... means
... to 'History' would record 'new' A2:C2 and 'old' D2:E2 ... as You have written!

option c)
This option compares NOW every ten seconds, is cell's C2-value has changed?
If it has changed then ... then code will record range A2:E2 to 'history'.
This will give different result than option b).

If there are open other Excel-file activated
... which do not have same sheets
... there will be conflict! = cannot record in named sheet that range!

Yes ...
Just notice Your 'minor' change...
with this Your the newest version,
You want to
... record from row 2

and
those sample results seems to have links to Input1-sheet
... which You want? = as You wrote, always same results
... or values from Input1-sheet?
My sample records values to row 6 - the newest record will be always top.

Good luck.
 
Back
Top