Hi all
Please help me for the following problem.
My spread sheet has a cell with XML data source from a website. Cell value changes every one minute. Now I need to draw the chart of these changes. When the value of this cell changes, Excel must automatically draw the continues of a line chart to show me the trend of changes.
Thanks in advanced
A.M
welcome to the forum.
Assumptions:
- Web based data table is in a worksheet named "data".
- A chart named "Chart 1" is already created in another worksheet named "chart".
- Chart 1's data source is cell A2 and down in worksheet "chart".
- Values in this range are copied from "F8" of worksheet "data" to the first blank cell below the existing data.
Try the Worksheet_Change Event code below. The code must be copied to "data" worksheet's code module. To do this, right click the tab name (data) then click "view code".
----- PS: because of pasting problems regarding IE application in my office computer, the code is copied here as 1 line. in order to split this 1 line into real code lines, i added 'NEW_LINE string in the end. delete this string with the leading single quotation mark and hit ENTER key to rebuild lines. ----- PPS: uploading a file will help us help you.Private Sub Worksheet_Change(ByVal Target As Range) With Worksheets("chart") .Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("data").Range("F8").Value .ChartObjects("Chart 1").Chart.SetSourceData .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With End Sub
Edited by SamT to add Paragraph breaks.
Last edited by SamT; 12-09-2013 at 11:01 AM.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Hello
Thanks
That works perfectly! Your solution to record a cell changes in a column is so interesting for me!
I have another question. Could you help me?
My problem is how to import data from a Non-HTML web page to an Excel worksheet. the web page shows many fields to the user but these fields are not in the form of HTML and they are not visible/unclosed so "Get external data" facilities of Excel are not able to read/extract the fields. is there any way to extracting data from these scripts-based web pages?
More thanks & Good Luck
you are welcome.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
@ mancubus
I don't know if it is the same problem but when I put code in a thread I had the same thing happening, all in one line.
I now copy the code into Notepad and from Notepad into the thread. That works for me.
If it is a different problem, sorry.
John
thanks John.
i informed the forum management on the problem.
it'se weird that i dont repeat the problem with my home computer.
another point is, i dont duplicate the problem with mrexcel, excelforum, etc that use the same platforms with my office computer.
i tried using notepad, notepad++ and write when i first faced with the problem. but they did not work for me.
so it's a mystery
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
mancubus.
I certainly don't have another suggestion but I guess we can be confident that management will get it sussed.
Have a good weekend.
John
that must be something related with internet security protocols of the company i guess. a couple of times i got a "blocked" message when i tried to access the forum.
nevermind. i'll survive.
thanks again.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Posting Permissions
Reply With Quote
