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:[color=blue]
> 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" <jonpeltier@yahoo.com> wrote in message
> news:3F53FEA3.7060101@yahoo.com...
>[color=green]
>>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:
>>[color=darkred]
>>>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.[/color]
>>[/color]
> I've
>[color=green][color=darkred]
>>>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*[/color]
>>[/color]
> programmatically.
>[color=green][color=darkred]
>>>We've just run a large online survey and the data is being fed back to[/color]
>>[/color]
> us in
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> for
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> extract
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> a
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> automatically.
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> not
>[color=green][color=darkred]
>>>yet investigated) the following options:
>>>
>>>1. Try to create a chart based on named-ranges in Excel, and just open[/color]
>>[/color]
> each
>[color=green][color=darkred]
>>>extract and copy and paste the data across. The chart will spring into[/color]
>>[/color]
> life
>[color=green][color=darkred]
>>>as the data is pasted. I'm not too sure what will happen if the size of[/color]
>>[/color]
> the
>[color=green][color=darkred]
>>>cut and paste areas are different.
>>>2. Write some sort of module in Excel VBA to either access the database[/color]
>>[/color]
> and
>[color=green][color=darkred]
>>>create charts for all the subsets automatically, or create a chart for[/color]
>>[/color]
> an
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> but
>[color=green][color=darkred]
>>>might give it a go if I thought I could create even one web page with[/color]
>>[/color]
> every
>[color=green][color=darkred]
>>>chart on it. I'd have to look at the format of the chart though, and how[/color]
>>[/color]
> I'd
>[color=green][color=darkred]
>>>get each chart back into Word and/or Powerpoint.
>>>4. Another mart, but perhaps using something like ThinSlicer to create[/color]
>>[/color]
> the
>[color=green][color=darkred]
>>>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[/color]
>>[/color]
> chart
>[color=green][color=darkred]
>>>object automatically when the report is opened on the network. This[/color]
>>[/color]
> would
>[color=green][color=darkred]
>>>require the entry of custom parameters for each unit, or a custom query[/color]
>>[/color]
> for
>[color=green][color=darkred]
>>>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
>>>
>>>
>>>
>>>[/color]
>>[/color]
>
>[/color]
