Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
hai!!
i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
Use the macro recorder as a guide. When I record a macro to create a simple
chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
meaning that doesn't need the looping statement?so,how to i check the size of
table that used to generate the chart? "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
thank you for your reply..its works,but then..my range for SourceData is
dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
How does the data get updated? Isn't it some piece of code? What is the
sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
My SourceData should be expand as i add new rows or new columns or both and
it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
You're solving the wrong problem. This approach might be part of the
solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
Sorry cause i didnt get what you meant..can you explain me more clear?
"Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
How do you update the dynamic data range?
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
I just select all from database..so,by time to time,there's may be a new
variable added..but i dont want to create a new chart..just update it.The no of row alwalys increase cause new rows of data will be add everyday. So,i will create an 'update' button on excel,and when i click it,it will get the latest data from database,put it in a table in excel and automatically create graph from the data in the table. Regards, linda "Jon Peltier" wrote: How do you update the dynamic data range? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
I see, no macro, it's a manual update.
Make sure the data range is clean, only data plus a header row with field names, the categories (X-axis labels or values) in the first column, the top left cell blank. Any other information in the sheet should be removed, or at the very least separated from the data by a blank row or column. Assuming you have only one chart on the worksheet, select a cell in the data and run this macro: Sub UpdateChartOnActiveSheet() ActiveSheet.ChartObjects(1).Chart.SetSourceData _ Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... I just select all from database..so,by time to time,there's may be a new variable added..but i dont want to create a new chart..just update it.The no of row alwalys increase cause new rows of data will be add everyday. So,i will create an 'update' button on excel,and when i click it,it will get the latest data from database,put it in a table in excel and automatically create graph from the data in the table. Regards, linda "Jon Peltier" wrote: How do you update the dynamic data range? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
Jon,
i've tried to run the macro that you gave but i got error. Run-time error '91':Object variable or With block varible not set. this is my current coding in macro. Sub CreateChart() Charts.Add ActiveChart.ChartType = xlColumnClustered ** ActiveChart.SetSourceData Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub the error is on the line that i put **. am i missing something that make it error?sorry cause i've no experience in macro. thanks~ -- Regards, Linda "Jon Peltier" wrote: I see, no macro, it's a manual update. Make sure the data range is clean, only data plus a header row with field names, the categories (X-axis labels or values) in the first column, the top left cell blank. Any other information in the sheet should be removed, or at the very least separated from the data by a blank row or column. Assuming you have only one chart on the worksheet, select a cell in the data and run this macro: Sub UpdateChartOnActiveSheet() ActiveSheet.ChartObjects(1).Chart.SetSourceData _ Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... I just select all from database..so,by time to time,there's may be a new variable added..but i dont want to create a new chart..just update it.The no of row alwalys increase cause new rows of data will be add everyday. So,i will create an 'update' button on excel,and when i click it,it will get the latest data from database,put it in a table in excel and automatically create graph from the data in the table. Regards, linda "Jon Peltier" wrote: How do you update the dynamic data range? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
Jon,
i already fixed the error..thank you very much..=) -- Regards, Linda "linda" wrote: Jon, i've tried to run the macro that you gave but i got error. Run-time error '91':Object variable or With block varible not set. this is my current coding in macro. Sub CreateChart() Charts.Add ActiveChart.ChartType = xlColumnClustered ** ActiveChart.SetSourceData Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub the error is on the line that i put **. am i missing something that make it error?sorry cause i've no experience in macro. thanks~ -- Regards, Linda "Jon Peltier" wrote: I see, no macro, it's a manual update. Make sure the data range is clean, only data plus a header row with field names, the categories (X-axis labels or values) in the first column, the top left cell blank. Any other information in the sheet should be removed, or at the very least separated from the data by a blank row or column. Assuming you have only one chart on the worksheet, select a cell in the data and run this macro: Sub UpdateChartOnActiveSheet() ActiveSheet.ChartObjects(1).Chart.SetSourceData _ Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... I just select all from database..so,by time to time,there's may be a new variable added..but i dont want to create a new chart..just update it.The no of row alwalys increase cause new rows of data will be add everyday. So,i will create an 'update' button on excel,and when i click it,it will get the latest data from database,put it in a table in excel and automatically create graph from the data in the table. Regards, linda "Jon Peltier" wrote: How do you update the dynamic data range? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to create a chart from a dynamic table of data?
Jon,
where to change the code if my chart and the source data is not in the same worksheet? -- Regards, Linda "Jon Peltier" wrote: I see, no macro, it's a manual update. Make sure the data range is clean, only data plus a header row with field names, the categories (X-axis labels or values) in the first column, the top left cell blank. Any other information in the sheet should be removed, or at the very least separated from the data by a blank row or column. Assuming you have only one chart on the worksheet, select a cell in the data and run this macro: Sub UpdateChartOnActiveSheet() ActiveSheet.ChartObjects(1).Chart.SetSourceData _ Source:=ActiveCell.CurrentRegion, PlotBy:=xlColumns End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... I just select all from database..so,by time to time,there's may be a new variable added..but i dont want to create a new chart..just update it.The no of row alwalys increase cause new rows of data will be add everyday. So,i will create an 'update' button on excel,and when i click it,it will get the latest data from database,put it in a table in excel and automatically create graph from the data in the table. Regards, linda "Jon Peltier" wrote: How do you update the dynamic data range? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... Sorry cause i didnt get what you meant..can you explain me more clear? "Jon Peltier" wrote: You're solving the wrong problem. This approach might be part of the solution, but it's not clear yet. It would be easiest to incorporate a few lines of code into the routine that updates the database. That's why I asked those questions in my last post. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... My SourceData should be expand as i add new rows or new columns or both and it will update the chart.i've tried to use the offset function but its just work for adding new row.Adding a new column does not effect the chart created.i use OFFSET with a defined name. date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1) sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1) i've try define another offset but it doesnt work.Got error 'the references is not valid.References for titles,values,or size must be in a single cell,row or column. acData-refers to:=OFFSET($A$1,0,0,COUNTA($A:$A),6) can these offset define in macro?if yes,where should i placed the code? regards, linda "Jon Peltier" wrote: How does the data get updated? Isn't it some piece of code? What is the sheet like after the update? Is the data table the only contents, or is there more stuff? Is the address of the output range known to the update code? The answer to your question is dependent on the answers to these (and probably more). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... thank you for your reply..its works,but then..my range for SourceData is dynamic,not static..so,i think i need to do 'the do while loop statement' but i dont have any idea about the coding. ur help is very appreciated,tq! regards, linda "Jon Peltier" wrote: Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C6") ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" End Sub Assuming your DB dump is on a new sheet, and applying some cleanup, this is a workable macro: Sub DB_Dump_to_Chart() Dim rChartData As Range Dim wsData As Worksheet Set wsData = ActiveSheet Set rChartData = wsData.UsedRange Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=rChartData ActiveChart.Location Whe=xlLocationAsObject, Name:=wsData.Name End Sub Naturally your recorded macro will be different, since you will record your actions while creating the type of chart you want from the data. But make the same kind of adjustments as I have made, and you should get a reasonable macro. For more on fixing up chart macros: http://peltiertech.com/Excel/ChartsH...kChartVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "linda" wrote in message ... hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dynamic range to create pivot table | Excel Discussion (Misc queries) | |||
How do I create a combination chart and table with different data. | Charts and Charting in Excel | |||
How do I create a dynamic chart | Charts and Charting in Excel | |||
How to create a dynamic chart using data from every 7th data row | Charts and Charting in Excel | |||
pivot table with dynamic base data | Charts and Charting in Excel |