Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
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* programmatically. 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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
Jon Peltier
http://briefcase.yahoo.com/jonpeltier None better than this fellow. "Alan" wrote in message ... 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* programmatically. 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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
His briefcase was empty but I'll do a search.
Cheers, Alan "Don Guillett" wrote in message ... Jon Peltier http://briefcase.yahoo.com/jonpeltier None better than this fellow. "Alan" wrote in message ... 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* programmatically. 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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
try here
http://www.geocities.com/jonpeltier/Excel/index.html "Alan" wrote in message ... His briefcase was empty but I'll do a search. Cheers, Alan "Don Guillett" wrote in message ... Jon Peltier http://briefcase.yahoo.com/jonpeltier None better than this fellow. "Alan" wrote in message ... 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* programmatically. 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
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
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 he http://www.geocities.com/jonpeltier/...kChartVBA.html and links to code for Pivot Tables he 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" wrote in message ... 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* programmatically. 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 |
#6
Posted to microsoft.public.excel,microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.inetserver.asp.general
|
|||
|
|||
Creating a large number of charts automatically (repost)
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" wrote in message ... 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* programmatically. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating multiple charts from large data file | Charts and Charting in Excel | |||
Large number of dynamic charts | Charts and Charting in Excel | |||
Repost:Automatically inserting a row in external workbook | Excel Discussion (Misc queries) | |||
Automatically creating the correct number of dated columns | Excel Discussion (Misc queries) | |||
Creating a large number of charts automatically | Excel Programming |