Inserting charts into many workbooks

Hi, 
    I'm using Excel 2007 and am pretty new to VBA.  I have a folder with 40 
or so workbooks- all with the same worksheet table format.  The worksheets 
are huge (20 000 + rows, 50 or so columns).  I need to open a workbook, make 
several fully formatted graphs, then insert the same graphs into every 
workbook , updated them with that workbooks' data.  

Any ideas on code for this? 
0
vbazen (1)
9/7/2009 11:48:01 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
203 Views

Similar Articles

[PageSpeed] 6

This isn't fully automated, but it will save some time. In one workbook, 
create the chart and format it to your specs. Then paste the chart into 
another workbook, and use the utility linked to in the following 
tutorial to change the series formulas, using the old and new workbook 
and sheet names of the host workbooks.

If the sheets are identically named, you only need to search for the 
workbook name enclosed in square brackets, and replace it with nothing.

How to Edit Series Formulas
http://peltiertech.com/WordPress/how-to-edit-series-formulas/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



vbazen wrote:
> Hi, 
>     I'm using Excel 2007 and am pretty new to VBA.  I have a folder with 40 
> or so workbooks- all with the same worksheet table format.  The worksheets 
> are huge (20 000 + rows, 50 or so columns).  I need to open a workbook, make 
> several fully formatted graphs, then insert the same graphs into every 
> workbook , updated them with that workbooks' data.  
> 
> Any ideas on code for this? 
0
jonNO1 (306)
9/8/2009 11:47:20 AM
Reply:

Similar Artilces:

EzPaste-xl2ppt Excel add-in, automating the copy/paste of charts and data
Hello, This is to notify about the release of EzPaste (http:// www.EzPaste.net), an Excel add-in intended to completely automate the transfer of charts and data from Excel to PowerPoint. EzPaste identifies automatically all the charts and the tables defined in the active workbook. The user then selects which of them he wants to paste to PowerPoint=AE , and EzPaste does all the work, would you have to paste one or one hundred tables/charts at once Even if you have to paste the selected range or chart, EzPaste does it with the click of a button Sorry if this message is a bit advertising, b...

Changing to Combination-chart from existing chart
I have made a Line-chart containing 8 different series. I now want to change 2 of the series to Bars, but in the same chart. How do I proceed to pick the wanted series and transform them to bars? Regards Jan Hi Jan, Right click a line series that you wish to change. From the popup menu select Chart type and pick bar. Cheers Andy Jan wrote: > I have made a Line-chart containing 8 different series. I > now want to change 2 of the series to Bars, but in the > same chart. > > How do I proceed to pick the wanted series and transform > them to bars? > > Reg...

Inserting specific images based on product.
Hi Everyone, I'm very knew to the CRM product and was curious... For example, I am customizing a CRM interface for a company with a wide range of products. They would like to add a tab to each of their product displays showing an image of that product. I added the tab (learning how thanks to this newsgroup) but I only know how to insert a graphic using an IFRAME in the form view which in turn displays the SAME image for all product listings. Does this make sense? Basically I'm asking...is there a way to display individual images for specific products, not strictly on the fo...

Too many problems while updating information online
Several of my accounts are unable to update information online using MS Money 2007 Deluxe - ING Direct - Bestbuy Card (HSBC) - MBNA - Bank of America This is frustrating!! Anyone else with the same problems? Just keep trying, eventually it will work. If you're in a bind, download the QIF or OFX files from the institutions web sites -- B of A works better that way anyway :) Paul McKenna <Paul McKenna@discussions.microsoft.com> wrote: >Several of my accounts are unable to update information online using MS Money >2007 Deluxe > >- ING Direct >- Bestbuy Card (HSBC...

Static Chart Data Range
Excel-2007, simple line chart: I have a chart that displays several columns of data for rows 2:84. Whenever I insert a new row #2, the new data range for the chart is now 3:85. How do I keep a static data range for my chart (2:84) whenever I insert new rows into the workbook? I have tried editing $A$2:$E$84 to A2:E84 and then saving the changes ... but it is an excercise in futility, it always reverts back to $A$2:$E$84. I would recommend creating a blank row 2, and hiding it. Then, when you insert a new row, you'll be inserting within the boundaries, instead of moving ...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Data labels for Excel 2007 Charts
Hi, I have charts that are based on dynamically populated data. I need the data labels for all of these charts to appear with 0 decimal places. The dynamic data populates with 2 decimal places (X.XX). Is there a universal setting I can apply to the worksheet or even the chart itself, so I do not have to go to each data point/data label and set the formatting to 0 decimal places? Thanks, Courtney ...

change exist chart to be dynamic
Hi all How can I change an existing chart to be dynamic? In series I saw a name in y axis only but no name of x axis. How can I create a series for x axis? Thanks in advance Daniel You need to understand how dynamic charts work. There are tutorials and links to more on this page: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Daniel" <Daniel@discussions.microsoft.com> wrote in message news:FF77A61A-58A9-4629-90E1-20742E6...

How do I fix a workbook.
I have a workbook with 5 sheets and macros with forms. When I select any one of the worksheets by the tab with the mouse, the cursor goes to a page and the only way I can get Excel to stop is to cancel the task. How do I either fix this workbook or copy all the sheets to another workbook? TIA -- Bill What version of Excel, Bill? Have you tried File-->Open and choose the Repair option? ************ Anne Troy www.OfficeArticles.com "bill" <bill@discussions.microsoft.com> wrote in message news:C5CBB482-1057-4B14-9277-B5FBAA9D866B@microsoft.com... >I have a workboo...

I cannot paste from one workbook to another. Copy works, paste do.
When I attempt to copy from one workbook and paste into another, copy appears to work but paste does not. The paste menu item is grayed out in the workbook to which I attempt to paste. Are there setting that can be changed to aloow the paste?? Check to see if either workbook and/or worksheet is protected. If so, unprotect. Then Copy/Paste should work. BTW, how are you copy?paste(ing) Tab or content? Dennis "JimmyMc" wrote: > When I attempt to copy from one workbook and paste into another, copy appears > to work but paste does not. The paste menu item is grayed out...

Interactive Chart Examples
I once was given an Excel file (protected of course - D'Oh!!!) where the person had charts that were interactive. There were various series and you scrolled thru a scroll bar to select the series you wanted. It would change the chart as you scrolled Can anybody point to samples that folks have done to do this. Previous posts have given a few links but I was hoping that someone has an example That I could use so I do not have to develop one from scratch. Thanks. Homer J. Simpson Hi, Have a look at these examples, Series and data http://peltiertech.com/Excel/Charts/Dynamics.h...

Insert Acount# in the PO print
Can some one please let me know where I should place this variable: "PurchaseOrder.Supplier.AccountNumber" in the PO.xml file I woud like to be able to print the Acount # in the PO Thank you jm wrote: > Can some one please let me know where I should place this variable: > "PurchaseOrder.Supplier.AccountNumber" in the PO.xml file > I woud like to be able to print the Acount # in the PO > Thank you > Into receipt header? Open your XML.. and find field date of receipt.. but why you have no AccountNumber into your PO.xml ? Antonio Thank you for a...

Excel2000: Strange behaviour for one cell in workbook
Hi The situation: There is a Excel workbook used for registering working time in firm departments, created earlier. The worksheets are protected, and I don't have the password, as author isn't working here anymore. The workbook is stored as read-only on network resource, and monthly every department is copying it to local computer and filling the table. The table's body has 2 rows data for every employee with 31 columns for month's dates, and several protected summary columns. For every date, working hours or some code string is entered into according cell. One of codes, and ...

Can i move multiple lines between workbooks
I have a .csv format file listing speed cameras, and the different speeds are identified by two characters at the end of each line - 30, 40, 50 etc. I would like to create separate files for each speed, but Excel won't allow me to cut/copy several lines to created a new file/workbook. Is there any way round this? Martin Ellis Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis <MartinEllis@discussions.microsoft.com>: > I have a .csv format file listing speed cameras, and the different speeds are > identified by two characters at the end of each line - 30, 40, 50 etc...

How do I get a chart to display backwards/forwards?
I have stock data. Row 35 is the nearest day. Row 36 is the next day, etc. When I chart the data using a 2 axes chart (custom), the chart doesn't display backwards. The nearest day displays on the left side of the chart, not the right. How do I change that? Double click on the axis, select the scale tab, and check "Values in reverse order" Jerry Will wrote: > I have stock data. Row 35 is the nearest day. Row 36 is the next day, etc. > When I chart the data using a 2 axes chart (custom), the chart doesn't > display backwards. The nearest day displays on the ...

Bar chart & Category (x) axis labels #2
If you make a bar chart in Excel 2003, and have two columns of text as Category (x) axis labels, the first column is shown vertical and the text in the second column is shown horizontal in the chart. If you open/make the same chart in Excel 97, the text in both columns is shown horizontal. If there is a lot of text, it becomes unreadable when it's vertical. Is this by design or can it be fixed somehow? ...

Visio 2003 Organizational Chart 2002 Compatibility
This is a multi-part message in MIME format. ------=_NextPart_000_0045_01C52B1D.54B10360 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have an organizational chart created in Visio 2003 Professional, and I = have saved the chart (with synchronized pages) in the 2002 file format. My problem is that when a Visio 2002 Standard user opens the file, the = pages are no longer synchronized, nor can the 2002 users expand and = collapse subordinates. Is this the norm, or what am I doing wrong here?=20 Thanks, Todd ------=_NextPart_000_0045_...

Pivot Chart Bar Width
Is it possible to stabilize the width of the bars when using a Pivot Chart? When there is very little data to chart - based on the values I select from page data pull-downs - the bars are really fat. Other times they are thin. I would really like to have a standard width. tia Pat Watson Pat - The bars are based on a percentage of the spacing between categories. If you have fewer categories, the bars get wider. If you decreased the bar width (by increasing gap width on the Other tab of the format series dialog), they'd be much further apart. You can't adjust these automatical...

Time-based floating bar chart
I have a problem similar to "Kaixi". I have data for elapsed times of backups, and I'm trying to chart the beginning and ending time of the backups for each server. The only problem is that each server may have more than one backup job, so I need to chart the elapsed time from the earliest start time to the latest end time, with gaps shown where there is no activity. The data looks like this: server start-time end-time abc 08:00 08:30 abc 08:00 08:45 abc 09:00 09:30 def 09:00 09:10 def 0...

SPC Control Charts
Does anyone know if Excel can create SPC control charts? I can not find them anywhere. Not as a basic chart type. You can roll your own. http://www.peltiertech.com/Excel/Charts/statscharts.html#CtrlCht has suggestions. Jerry Skip wrote: > Does anyone know if Excel can create SPC control charts? > I can not find them anywhere. ...

How Many Days Since... Formula -Help
Hi, can anyone help? I have a spreadsheet tracking several different Departments in our company and how often they have an accident. I need to track how many days they go without an accident. So if Dept A had their latest accident yesterday. And if someone opens the spreadsheet in 3 days, it should say "4 days w/o an accident for Dept A. I'm sure this can be done, I just have no idea how :roll: Thanks in advance, Scotty Assuming 8/5/2003 is the start date... =TODAY()-"8/8/2003"&" Days w/o an accident for Dept A" Lance >-----Original Message----- &...

How to limit insertion of records
I have two tables as follows: TABLE1: (BUSINESS TYPE) ===== ID Business Type (With a lookup choice like: Individual, Partnership, Company) TABLE2: (OWNERS) ===== ID Business Type (Looked up from Table1) Owners I want the Table2 based subform, to allow only a SINGLE record to be entered if an INDIVIDUAL business type is selected in the Table1 based main form, Maximum of SEVEN records if PARTNERSHIP business type is selected in the Table1 based main form, and UNLIMITED records if COMPANY business type is selected in the Table1 based main form. Any guidance shall be highl...

insert vertical grid line that is linkedto a certain data element
Hi. I have a chart with the performance of certain stocks (x=date, y=value). I add data to this chart weekly, and plot the year-to-date performance as well. I would like to add grid lines at the start of each year: a vertical grid line that is linked to a certain date and moves when additional dates are added. Also, the number of data points between each vertical grid line is not fixed: it differs per year. Anybody has a clue how to get this implemented? Thanks! Henk-Jan Hi, You can add vertical lines to line charts or scatter charts, but they are slightly diffierent techniques. P...