Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups. I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to create over 100 Excel *charts* programmaticall y.
We've just run a large online survey and the data is being fed back to us in
a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking for
a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily extract
each required subset. The output of the extract process will probably be
spreadsheets in TSV format.
Last time I had to graphically report this sort of data I loaded it into a
mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the charting
requirements if it'll mean I'm able to create these charts automatically.
Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but not
yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open each
extract and copy and paste the data across. The chart will spring into life
as the data is pasted. I'm not too sure what will happen if the size of the
cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database and
create charts for all the subsets automatically, or create a chart for an
individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC but
might give it a go if I thought I could create even one web page with every
chart on it. I'd have to look at the format of the chart though, and how I'd
get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create the
charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the chart
object automatically when the report is opened on the network. This would
require the entry of custom parameters for each unit, or a custom query for
each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.
Regards,
Alan
5  3171 
Jon Peltier http://briefcase.yahoo.com/jonpeltier
None better than this fellow.
"Alan" <Xa************ *@XparadiseX.Xn etX.XnzX> wrote in message
news:O4******** *****@TK2MSFTNG P11.phx.gbl... Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups. I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to create over 100 Excel *charts* programmaticall y.
We've just run a large online survey and the data is being fed back to us
in a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking
for a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily
extract each required subset. The output of the extract process will probably be
spreadsheets in TSV format.
Last time I had to graphically report this sort of data I loaded it into a
mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the charting
requirements if it'll mean I'm able to create these charts automatically.
Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but not
yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open
each extract and copy and paste the data across. The chart will spring into
life as the data is pasted. I'm not too sure what will happen if the size of
the cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database
and create charts for all the subsets automatically, or create a chart for an
individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC
but might give it a go if I thought I could create even one web page with
every chart on it. I'd have to look at the format of the chart though, and how
I'd get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create the
charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the
chart object automatically when the report is opened on the network. This would
require the entry of custom parameters for each unit, or a custom query
for each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.
Regards,
Alan
His briefcase was empty but I'll do a search.
Cheers,
Alan
"Don Guillett" <do*****@281.co m> wrote in message
news:Ox******** ******@TK2MSFTN GP11.phx.gbl... Jon Peltier
http://briefcase.yahoo.com/jonpeltier
None better than this fellow.
"Alan" <Xa************ *@XparadiseX.Xn etX.XnzX> wrote in message
news:O4******** *****@TK2MSFTNG P11.phx.gbl... Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups.
I've posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to create over 100 Excel *charts*
programmaticall y. We've just run a large online survey and the data is being fed back to
us in a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking
for a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily
extract each required subset. The output of the extract process will probably be
spreadsheets in TSV format.
Last time I had to graphically report this sort of data I loaded it into
a mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the charting
requirements if it'll mean I'm able to create these charts
automatically. Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but
not yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open
each extract and copy and paste the data across. The chart will spring into
life as the data is pasted. I'm not too sure what will happen if the size of
the cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database
and create charts for all the subsets automatically, or create a chart for
an individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC
but might give it a go if I thought I could create even one web page with
every chart on it. I'd have to look at the format of the chart though, and how
I'd get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create
the charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the
chart object automatically when the report is opened on the network. This
would require the entry of custom parameters for each unit, or a custom query
for each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.
Regards,
Alan
try here http://www.geocities.com/jonpeltier/Excel/index.html
"Alan" <Xa************ *@XparadiseX.Xn etX.XnzX> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. .. His briefcase was empty but I'll do a search.
Cheers,
Alan
"Don Guillett" <do*****@281.co m> wrote in message
news:Ox******** ******@TK2MSFTN GP11.phx.gbl... Jon Peltier
http://briefcase.yahoo.com/jonpeltier
None better than this fellow.
"Alan" <Xa************ *@XparadiseX.Xn etX.XnzX> wrote in message
news:O4******** *****@TK2MSFTNG P11.phx.gbl... Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have to code a solution to this
it'll probably be done in ASP on a web server, unless there's a
significantly better way.
I'm looking for a way to create over 100 Excel *charts* programmaticall y. We've just run a large online survey and the data is being fed back to us in a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will
require that just over 100 separate charts will have to be created. I'm
looking for a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily extract each required subset. The output of the extract process will probably
be spreadsheets in TSV format.
Last time I had to graphically report this sort of data I loaded it
into a mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the
charting requirements if it'll mean I'm able to create these charts
automatically. Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but not yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open
each extract and copy and paste the data across. The chart will spring into
life as the data is pasted. I'm not too sure what will happen if the size
of the cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the
database and create charts for all the subsets automatically, or create a chart for an individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used
OWC but might give it a go if I thought I could create even one web page with
every chart on it. I'd have to look at the format of the chart though, and
how I'd get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create the charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the
chart object automatically when the report is opened on the network. This would require the entry of custom parameters for each unit, or a custom
query for each, and unless the chart is somehow cached, would only display if
the report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos
etc.
Regards,
Alan
Alan -
Put the code into a regular code module, not the code module that backs
up the sheet. I have some code suggestions for working with charts here: http://www.geocities.com/jonpeltier/...kChartVBA.html
and links to code for Pivot Tables here: http://www.geocities.com/jonpeltier/...s/pivotvba.htm
I would suggest making regular charts from data within pivot tables;
pivot charts forget their formatting every time you refresh the
underlying table.
- Jon
-------
Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html
_______
Alan wrote: Thanks Jon. Would a VBA code module in the main results spreadsheet be the
best way to attack this? Where's a good place to look for a crash-course on
programming Pivot Tables and Charts?
Thanks for the help,
Alan
"Jon Peltier" <jo********@yah oo.com> wrote in message
news:3F******** ******@yahoo.co m...
Alan -
It sounds like pivot tables based on the survey results are the way to
go. You can automate the pivot tables to extract your "subsets", then
define named ranges based on columns or rows of the PTs, and base your
charts on these ranges; the pivot table can be based on interactive user
input. When each chart is done, you can then export it to Word or
PowerPoint, then go on to the next chart; alternatively, you can just
keep piling up charts and export them all at the end, though that many
charts can lead to trouble.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
Alan wrote:
Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups.
I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to create over 100 Excel *charts*
programmaticall y.
We've just run a large online survey and the data is being fed back to
us in
a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking
for
a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily
extract
each required subset. The output of the extract process will probably be
spreadshee ts in TSV format.
Last time I had to graphically report this sort of data I loaded it into
a
mart and used PivotCharts. The owners of they survey don't need the
flexibilit y of Analysis Services so I'm happy to pre-define the charting
requiremen ts if it'll mean I'm able to create these charts
automatically.
Manipulati ng 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentati on with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but
not
yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open
each
extract and copy and paste the data across. The chart will spring into
life
as the data is pasted. I'm not too sure what will happen if the size of
the
cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database
and
create charts for all the subsets automatically, or create a chart for
an
individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC
but
might give it a go if I thought I could create even one web page with
every
chart on it. I'd have to look at the format of the chart though, and how
I'd
get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create
the
charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the
chart
object automatically when the report is opened on the network. This
would
require the entry of custom parameters for each unit, or a custom query
for
each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.
Regards,
Alan
While I may be missing some points, it seems the easiest way would be:
A) Get all normalized data into a SQL server table;
B) Produce datasets with 'select transform' queries;
C) Invoke Excel from SQL server and produce charts;
D) Export charts as bitmaps.
BY maximizing the data processing on the SQL server side and having Excel to
produce the charts only you will save yourself a lot of grief and processing
time.
Cheers,
FK
"Alan" <Xa************ *@XparadiseX.Xn etX.XnzX> wrote in message
news:O4******** *****@TK2MSFTNG P11.phx.gbl... Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups. I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to create over 100 Excel *charts* programmaticall y.
We've just run a large online survey and the data is being fed back to us
in a single spreadsheet - each respondent on a row and their dimensions
(division, department, etc) and each of their responses in columns.
This data has to be sliced by a number of dimensions - this will require
that just over 100 separate charts will have to be created. I'm looking
for a way to create these charts in the most efficient way. I'm happy to
normalise the data and load it into SQL Server, and can then easily
extract each required subset. The output of the extract process will probably be
spreadsheets in TSV format.
Last time I had to graphically report this sort of data I loaded it into a
mart and used PivotCharts. The owners of they survey don't need the
flexibility of Analysis Services so I'm happy to pre-define the charting
requirements if it'll mean I'm able to create these charts automatically.
Manipulating 100 PivotCharts was too much for them. Each chart will
ultimately be fed back to users in a Word document or Powerpoint
presentation with the appropriate analyses.
How do I then create a chart for each subset? I've thought about (but not
yet investigated) the following options:
1. Try to create a chart based on named-ranges in Excel, and just open
each extract and copy and paste the data across. The chart will spring into
life as the data is pasted. I'm not too sure what will happen if the size of
the cut and paste areas are different.
2. Write some sort of module in Excel VBA to either access the database
and create charts for all the subsets automatically, or create a chart for an
individual subset after accepting some sort of input from the user.
3. Use Office Web Components to do the charting on-line. Never used OWC
but might give it a go if I thought I could create even one web page with
every chart on it. I'd have to look at the format of the chart though, and how
I'd get each chart back into Word and/or Powerpoint.
4. Another mart, but perhaps using something like ThinSlicer to create the
charts online.
5. Pay a student $15/hr to manually create a chart for each subset.
6. Include some code in each Word document that manages to create the
chart object automatically when the report is opened on the network. This would
require the entry of custom parameters for each unit, or a custom query
for each, and unless the chart is somehow cached, would only display if the
report is opened with access to the network/database. OTT?
7. Something else I haven't yet thought of.
Any ideas? I be really grateful for thoughts, links to tutes/HowTos etc.
Regards,
Alan
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Thomas Guettler |
last post by:
Hi!
I need to create some simple charts with python.
A list contains integers between 0 and mymax.
I want to see how the values are distributed (How
many are 0, how many are mymax, ...). The result should
be a small (200x200) png file.
I searched a bit:
|
by: Alan |
last post by:
Hi there,
Are there Excel charting gurus here?? If so then please read on...
Sorry for the cross-post but I'm not familiar with the Excel groups. I've
posted to asp.general because if I have to code a solution to this it'll
probably be done in ASP on a web server, unless there's a significantly
better way.
I'm looking for a way to...
|
by: nospam |
last post by:
Hi,
I have an application whose textareas rows automatically resize onFocus
to the number of lines in the textArea.
The cols are set to 100% (in a CSS file) in order to always take the
full browser width
I use textarea.value.split('\n') to find the number of lines.
Some lines are longer than the 100% width and they wrap.
However, wrappeed...
|
by: vanisathish |
last post by:
Hi All,
Is there a way to draw charts from SQL Server Data using ASP.?Is it
possible to use the SQL Reporting Services using ASp
|
by: Good Man |
last post by:
Hi there
I'm developing a large web application. Part of this web application will
be storing numerical chart data in a MySQL table - these numbers will be
already calculated, and are just being stored for reference.
In this particular table, the stored data will never be deleted or
changed. The only actions performed will be SELECTs and...
| | |
by: matvdl |
last post by:
I have migrated my asp application to asp.net some time ago - but I am still
having some difficulties in understanding the best way to mange some tasks.
I currently have a page that loads a aspx web page - this page is
continually refreshed - every 5 seconds or so. To do this I use the download
behavior on the client to call a particular...
|
by: Gordon |
last post by:
Hello again,
Sorry to repost this request, but I'm under a bit of pressure to find a
quick solution.
All I basically want is an automatically updating link (OLE, not DDE)
between a control in my application and a cell in an Excel spreadsheet. My
control has to automatically receive updates from the spreadsheet cell.
I already know how...
|
by: implicate_order |
last post by:
Greetings,
I'm new to python and am in the process of writing a script to parse
some CSV data, spread it across multiple Excel worksheets and then
generate charts. I searched the internet to find some place where I
could look up a HOWTO doc/recipe to do that using either pyExcelerator
or win32com.client.
Could someone point me in the...
|
by: Ric |
last post by:
I have a mandate from a customer to create custom bubble and gannt charts
that are viewable in their Web browser. We explored some of the charting
tools and controls but because of the complexity of the charts these were not
sufficient.
I would like some feedback/suggestions from the dev community on ways to
create custom charts.
I have...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
