Inserting charts into many workbooks
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?
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 i...Setting series values on Excel Chart
I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet.
Unable to set the Values property of the Series class.
The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid.
My code is:
1. Retrieve selected data from database and place it at the top of the sheet
2. Add The Chart
3. Iterate through the data (amount can be variable depending upon user inputs)
For j as i...How do I make a 'box and whisker plot' graph in Microsoft Excel?
I am in Year 12 and am doing a stats assignment. The project states that i
MUST insert a box and whisker plot for some data, but i don't know how to do
this. Does anybody know?
Here are some sites on the topic of stats charting not just box and whisker.
Andy Pope, Microsoft MVP - Excel
"Year 12 student who needs help" &...Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the
"code" field is =>20. I appreciate suggestions! Deadline Monster is lurking!
User enters the job processing endcode value (numeric) into the "code"
field. If the endcode is =>20, comments are required.
(P.S. I don't know VB)
You would put your validation code in the Form's BeforeUpdate event.
If Me.EndCode >19 Then
If Len(Me.Comments & "") = 0 Then
MsgBox "Comments are required"
Cancel = True
...How do I make hyperlink target scroll to the top-left of spreadshe
I can't remember the setting to make Excel automatically scroll my
hyperlinked cell to the top-left. For example, if my hyperlink is in cell A1
and the hyperlink destination is cell Z30, when you click the hyperlink in A1
the cell Z30 is now displayed in the top-left of the spreadsheet.
It seems like there was a checkbox in the Options dialog box, but now I
can't find it.
I did this awhile ago, but forgot to write down the information.
On the Transition tab
Transition navigations keys
Regards Ron de Bruin
"Lori W....How to create a ledger sheet
Hello. I have a database with the following tables:Customers, Orders, and PaymentsThe Customers table has a one-to-many relationship with the Orderstable. The Customers table also has a one-to-many relationship withthe Payments table.I am trying to create what I think is commonly called a ledger sheet.This sheet simply shows the dollar amount of every order that anindividual customer has placed (debits) and every payment that thecustomer has made (credits). It also shows a running balance. Entriesare shown in chronilogical order.I am having trouble creating a report that would draw debits from...Printing Multiple Receipts at a Time From Journal
Is there a way to print multiple journaled receipts at a time? Say all
receipts for a batch or by date or other criteria?
Not that I've seen.
Get Secure! - www.microsoft.com/security
You must be using Outlook Express or some other type of newsgroup reader
to see and download the file attachment. If you are not using a reader,
follow the link below to setup Outlook Express. Click on "Open with
under the MS Retail Management System on the right.
"Tom Bombadill" <Genius_pos...How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to
Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three
different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2
cases 2 bottles if possible 2.2 in a case column.
See http://www.cpearson.com/excel/fractional.htm for details.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in
>...making an equation in access
Hi, I would like to create a field that works out a formula based on other
fields. i.e field z=field4, when field 1<field 2 - field 3 etc. Yes, it may
be a silly question but I am only new to this....Thanks, Dani.
recommend against creating a "field" in a table and storing this value. One
of the generally accepted rules of relational database development is not to
store a "computed" (any value that is based on the value of other fields in
your database) value in the table. It is a waste of database space, and will
eventually result in bad data (...I get COMException while exporting chart on IIS 6.0.
I have this code that works perfectly on my development machine when I
deployed it at customer site it gets exception. Here is the error and my code:
Exception from HRESULT: 0x800A03EC.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: Exception
from HRESULT: 0x800A03EC.
An unhandled exception was generated during the execution of the c...chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under
Vista. I can select a line style, for example, long dashes.
However, if I try to change the axis (change from "automatic" to
"fixed" on the horizontal axis), the line on the chart immediately
becomes solid again. The legend still shows the proper dashing.
I can get the dashing partly back by making the line thinner,
but only where the variation is fastest - regions where
the derivative is near zero are still solid even for thin lines.
I'll appreciate any help!
I was not able to reproduce this.
Can you...How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of
the ranges all charts in my view refreshes and it takes much time. My pc is
aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to
force the charts not to update all the time?
...Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed
e.g cell b2 has the word red it it, in cell c2 i would like a counter for
everytime the cell in b2 has changed, at the end of the day you get final
number. Is this at all possible?
You can do it by putting the following VBA macro in your sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Cells(2, 3) = Cells(2, 3) + 1
> I would like to count the number of times a cell has bee...formula to get sheet name
can a formula retreive a worksheet name or does it need to be macro based?
This formula that takes care of the possible situation whereby you have only
one sheet in the workbook and its name is the same as the workbook's. Note:
CELL("filename") will only work if the file has been saved at least once and
if the file is opened in a different language system, the argument
"filename" will need to be changed manually to the corresponding word (e.g.
in Spanish "nombrearchivo"):
=LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...Inconsistent Chart Sizing Into PPT
Can't get charts to paste into PowerPoint with consistent size. To test, I
expanded (using the corner handles) two charts to exactly fill the screen.
When I copy/paste each chart into an individual slide in PowerPoint, one is
noticibally larger than the other. Is there a way to make the charts the
same size and paste the same size?
I have an article on this in the works, due out in about 24 months, the
rate I'm going.
A. Use embedded charts, and use Copy Picture (hold shift while selecting
Edit menu, os Copy turns to Copy Picture) with On Screen and As Picture
B. ...Charting dynamic range
Ok let me say i fully understand how to name a range and in the source data
use the formula =sheetname!rangename
Problem arrises when my macro that runs an advanced filter with the source
data and add data to the bottom of the range the chart reverts back to it's
initial data source =sheet1!$a$4:$b$15
so even though it accept the named range intitailly once the advanced filter
runs it almost ommits the named range. I am using 2007 and i never had this
problem in 2003.
What suggestions do you have for me?
...Time Delayed Response to MsgBox
I have a several "research" files that I set up to run a macro as soon as a
file is opened. In each case the macro retrieves and summarizes a bunch of
data taking several minutes to execute. I'm setting up a "master" file with
a simple macro that will open and close each of the "research" files
sequentially so I can run the series of reports without being at the computer
There are times where I want to open a "research" file to review the data
but not execute the macro. I'd like to add a MsgBox that prompts the user t...How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be
able to track when a student has entered data into specific cells of the
spreadsheet. Any ideas?
In the code behind the worksheet, enter (eg)
Private Sub Worksheet_Change(ByVal Target As Range)
Cells(1, 1).Value = Now()
This will enter in Cell A1 the date and time at which any entry is made in
If you need the location of the time-stamp to vary according to which cell
is changed then you can test the value of Target and vary the destination
Return email address is n...Attendence sheet
Does anybody has Attendence excel file which i can use for tracking
Nitin, see if one of these will work for you
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Nitin Kamurlekar" <firstname.lastname@example.org> wrote in message
> Does anybody has Attendence excel file which i can use for t...Default font in charts
Is there any way to change the default font in charts? ie, I'd like every
axis and title to be 16pt Times New Roman right off the bat. Thanks.
"Alex" <Alex@discussions.microsoft.com> wrote in message
> Is there any way to change the default font in charts? ie, I'd like every
> axis and title to be 16pt Times New Roman right off the bat. Thanks.
Set up a chart the way you want it to look. Right-click in its chart area
and choose Chart Type. Click the Custom Types tab and hit the "set as
default...Can't insert rows
I am using Excel 2007 and have just loaded a spreadsheet created in a
previous version which is running in Compatibility mode. When I try to
insert a row I get an message "Cannot shift objects off sheet".
How do I insert rows on this sheet? No problem with another workbook loaded
at the same time which was created in 2007.
See if this helps
"Cannot shift objects off sheet" error message when you hide columns in
(there's no email, no snail mail, but somewhere should be gmail in my...Run-time error 1004
I am using Excel 97 on NT and have a command button on a sheet to whic
I attached a macro for copying the sheet to a new workbook, deletin
specific columns and other unnecessary info, and saving the new book t
a network drive under a name with today's date for emailing to anothe
When I perform the function manually everything works beautifully bu
when I attempt to execute the macro I get the following error
"Run-time error '1004': Copy method of worksheet class failed". when
use the Debug button, VBA highlights Sheets("Daily").Copy as th
culprit. Macro f...Dynamic chart labels
I am using a dynamic bar chart and want to add a label to an individua
data point. Is there a way to do this so that the label follows th
data point as the chart updates
danoPosted from http://www.pcreview.co.uk/ newsgroup acces
A datalabel will follow the datapoint and update as appropriate. To add
data labels to a series, double click the series and click on the Data
Labels tab. To add data labels to a single point, select the series then
the point (two single clicks), then double click on the point, and again
click on the Data Labels tab.
Jon Peltier, Microsoft Ex...show last data point in chart
I am charting a range of observations/data points. Is
there a way to make the last data point show up
differently on the chart (different color/shape)?
> I am charting a range of observations/data points. Is there a way to make
the last data point show up differently on the chart (different
Click the charted data once to select the entire data series. Pause. Click
the single point to select it. Then use the Format menu.
Thanks for your reply.
Well, that would work if I knew which point on the chart
I would like to know if there is a way to set up when I enter the data
each cells on first row, it will default the date and time stamp on
first row. 2nd row should have the different date and time stamp than
I put "=now()" but it create the same date and time for 80 rows. I
don't want that. I want to enter each rows and each rows should be
different by one minute apart but same date - June 22.
Your help would be much appreciated.
Format A1:A79 as Custom dd-mmm-yy h:mm
Hold down CTRL Key and hit semi-colon key then <space> bar.