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!

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!

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!

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!

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!

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!

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!

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!