How to add series with VBA?

How can I add multiple series to an existing Excel graph using automation
from Access 2000?  I'm trying to set up a loop that will define the Xvalue
and YValue for each series, but not sure how.

I'm creating up to 100 Excel worksheets at a shot with data from Access
2000, and each worksheet needs to have a graph with multiple series.  The
worksheet creation loop looks like this:

For i = 1 to sn.Count
db.Execute "SELECT * INTO [Excel 8.0;Database=" & strXlsPath & "].[" & _
    strSheetName & "] FROM tblExcelData", dbFailOnError

Then I create an embedded chart like this:

Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
Set objChart = objSheet.ChartObjects.Add(Left:=170, Top:=12, Width:=500,
    With objChart
        .SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
            Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _
            (strXlsFile).Worksheets(sn(i)).Range("B1").End(xlDown).Row), _
        .ChartType = xlLineMarkersStacked
         .Parent.Name = sn(p) & "_Chart1"
         .HasLegend = False
         .HasTitle = True
         .ChartTitle.Text = sn(i)
         .ChartTitle.Font.Bold = True
         .Axes(xlCategory, xlPrimary).HasTitle = False
         .Axes(xlValue, xlPrimary).HasTitle = False
         .Axes(xlCategory).TickLabels.Font.Size = 10
         .Axes(xlCategory).TickLabels.Orientation = 90
    End With

Now I need to add several more series - here's where I'm lost.

Do While s < col.Count
    With objChart.SeriesCollection.NewSeries
       .Name = "s" & s
       .Values = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("E" & s
& ":E" & lr)
       .XValues = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("E" &
s & ":E" & lr)
    End With

How do I set the Value and XValue?  Could this be done with a string using
the R1C1 reference style?  How?  Do I need a separate loop to add the
additional series, or can it be done while setting up the first series?

Thanks in advance.

deko (1)
3/8/2005 2:05:23 AM
excel.charting 18370 articles. 0 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 54


Similar Artilces:

Question re. use of animation rebuild VBA
Hello, I am preparing a interactive ppt for use at a kiosk and would like to utilize the following VBA code into a macro in order to rebuild animation on previously viewed slides: Sub ResetSlide() ' The number after GotoSlide is the slide number. SlideShowWindows(1).View.GotoSlide 1, msoTrue End Sub (Dumb) Q#1: Does the part of the code that says " ' The number after GotoSlide is the slide number." stay in the code or is that just an instruction to the code user? Either way, does the (number) need to be changed for each situation? Q...

office shortcut bar
I see through a Google search that the geniuses at Microsoft did not put the Office Shortcut Bar (OSB) in Office 2003. I like and use the Windows Quick Launch Bar for my applications. After several years of using ACT! (3.x through 2005) to manage my business I am migrating to Outlook 2003 (with Business Contact Manager). I remember from an old work PC that, via the Office '97 Shortcut Bar, I could quickly add tasks and calendar items to Outlook (without opening Outlook). How can I directly access the tasks and calendar to add items via the Windows Quick Launch Bar? Since I did a clean inst...

Permissions Add Distribution Group members
We have an SBS 2003 w/ XP clients environment. I was wondering if there is a way to allow a specific user to add/remove members to any distribution groups that are created. I found that one user can add/remove members by adding that user as the manager of each individual group but I was wondering if there was a way to give more than one member this ability and to also not have to go to each distribution group and configure this permission. Thx! In, rfIPS <> typed: > We have an SBS 2003 w/ XP ...

how do i set up a footer to add under every mail i send
how do i set up a footer to add under every mail i send Signatures ? see Outlook help "sadrk" <> wrote in message > how do i set up a footer to add under every mail i send ...

Select Entire Field
How do I select an entire field with VBA? My user clicks on the field (not tab field), gets a warning, and focus is set back to the field -- but the cursor does not select the entire field. This is a decimal entry/percent and would be easier for user to just type entry and not have to select the field or make adjustments. Using Access 07 and database option is set to Select Entire Field. Very much an amateur in need of aid... thanking you in advance, Greg Actually Greg, but Chris is here, too! Thanks for responding and the suggestion. Here's my code... Me.txtDiscount.SetFocus Me...

Excel VBA #7
HI, I need some help building a macro. In excel let's suppose that I have the following table: Product Description Quantity Path to details Product 1 0\\server\product1_description.doc Product 2 1\\server\product2_description.doc Product 3 2\\server\product3_description.doc Now, I want to have a button that when someone clicks on it, it will call a macro that verifies the Quantity column and if the quantity is >=1 it will generate a word document and merge the product des...

In table, add column to the right, but then can't size it
In a Word 2007 table, I have my page layout set to Landscape Orientation for printing. I want to add a narrow new column on the right. I select the current right-most column, then right-click and choose Insert, then Insert Columns to the Right. However, the column Word adds takes up all remaining horizontal space on the page, past even the right margin of the page. The right border of the table (and of that new column) is now not visible, so I can't get to it in order to grab its sizing handles to decrease the width of that added column. I can't see the right border of th...

Running check with VBA on multiple fields before updating
I'm trying to run a check on two fields so I can ascertain if the third field should be updated or if a new line should be added. I'm using the index and seek method to try and handle this. The problem is that the first match it comes across the field gets changed. This is my code. Dim db As Database Dim rec As Recordset Dim strSQL As String strSQL = "tblpos" Set db = CurrentDb() Set rec = db.OpenRecordset(strSQL) 'Do Until rec.EOF rec.Index = "emp" rec.Seek "=", Me!Combo257 If rec.NoMatch = False Then 'Do Unti...

Can't add a new user
When I attempt to add a user to CRM 3.0, I get the error: You are attempting to create a user with a domain logon that is already used by another user. Select another domain logon and try again. We think this user had access to the system at one time but his CRM account was deleted. What old data is still around that's preventing me from adding him back? Is it in the CRM database, AD, the registry? What do I need to do to correct the problem? Thanks for your assistance. Hi Ed, since you can not delete a user from CRM (you need to go directly to the database to do that) I guess...

Signature Auto-add Kills Spell Check in Outlook New Message
Outlook 2003 with Word 2003 as the email editor When I set a signature to auto-add for a new mail message, it also stops the spelling check for new messages. If I look in Outlook, the New Message window > Tools > Language > Set Language > the box, "Do not check spelling and grammar" is checked. Depending on where I place the insertion cursor, this box is checked or unchecked. If I place the insertion cursor *below* the signature and then look at the box, it's unchecked. But if I place the insertion cursor anywhere else, in the sig or above, the box is checked. Acc...

want to create an application in Excel where I want to populate class variables in listviews and then display measures and time dimension in an excel sheet respectively in Rows and Columns depending upon the selection made in the listviews for the class variables.Each time the selection in listviews will change the report shoud be re generated /refreshed. Any suggestion /example or VBA code will be a great help. Regards ...

How do I get a query to add an incrementally increasing number?
I have a query that selects a subset of records. I need to add a "run number" to that subset, starting with 100. That is, the first record is 100, the second 101, etc. I know there has to be a way to do this, but I can't figure it out You might see if you can get this code to work for you: I have not tried it out... Tom Wickerath Microsoft Access MVP __________________________________________ "RNDL" wrote: > I have a query that selects a subset of records....

How do I add command buttom?
I've written a macro that I want to fire using a command button. I wrot a macro that adds the button - and one that deletes it just fine However I am having problems automating the adding and the deleting o the button on the standard toolbar - I only want it available for particular workbook. I thought I should be using an event procedure. So I've trie different combinations of: Open Before close Workbook activate deactivate. In the event procedures I am either calling a procedure in a standar module that adds the button, or a procedure that deletes the button o the standard to...

Add images locally, view over network
I am developing an Access 2003 application where users must insert their signature (as an image) onto a form. Each user would have their signature stored as an image on the C:\ drive of their computer. This image is added to an imagebox by using the FilePicker dialog. I can view the signature that I added off of my C:\ drive but it is not viewable on other users computer's. I am concerned about storing my signature in a shared network directory. How can I have users add an image from their C:\ drive and have other users view this signature when the form is opened over the networ...

Cannot get VBA modifications to show up under Vista
I have a dynamics.vba file that contains VBA code for customizations on a Vista Business edition machine. For some reason, the vba "dot" doesn't appear on any of the specialized forms, even though the vba file is present in the subdirectory. If I log into the machine as Administrator, the dot shows up. I've added the user in qeustion to the local Administrator's group on the machine, and given her ownership of the entire GP folder, yet she cannot get it enabled. The only other option seems to be adding her usernamer to Domain administrator, but that is an absolute la...

Different error bar values within a data series?
I am trying to put error bars into my graph but need the values to be different. However, if I change one point it changes all of the points? Hi, Create two columns, one for '+' errors and the other for '-' errors. Rightclick on any data-point on your graph, "Format Data Series" --> select the appropriate "Error Bars" Tab --> under Error Amount check "Custom" and enter the ranges for the '+' and '-' values --> "OK" Regards, B. R. Ramachandran "Izzy" wrote: > I am trying to put error bars ...

Statistics functions in VBA
Hi All, I've created a sheet in which I would like to calculate mean and standard deviation for a user selected range. I have a number of ranges of data (arranged in columns and of varying length) which the user may select. Once the user has selected a range, I'd like them to be able to click a button to run a macro that will display the mean and (as a string maybe) in the cell immediately below and to the right of the lowest cell selected by the user. Example, the user selects the range (C24:C31), runs the macro and ends up with the mean and in cell D32. The ...

Where is the CRM add-in Mail Merge button (Word 2007)?
Hi, I'm trying to do a mail merge (contacts) with a default mail merge template. When the word document appears, I can't see the CRM button on the add-in section. mailing list menu option it doesn't works either, appears disable. When I do this on a VPC Test Server from my computer this works fine, I can see the CRM Add-in Button...when I access to my developer enviroment doesn't work. Why? addional info: 1. Working with Word Version 2007 2. I'm using the Administrator Role on both scenarios. What am I do missing? Hi, Did you find a solution for you problem ? It...

Reference Column of Named Cell vba
I have named a cell on sheet - how doe I reference just the column property of that named cell in vba on another sheet in the same workbook please ? Thanks Dim myCell as range set mycell = worksheets("somesheetname").range("SomeRangeName") Then you can use something like: msgbox mycell.column Isis wrote: > > I have named a cell on sheet - how doe I reference just the column property > of that named cell in vba on another sheet in the same workbook please ? > > Thanks -- Dave Peterson Dave Peterson <petersod@verizon...

Off-line add in for Outlook?
Is there an add-in for Outlook that will ask whether you want to launch off-line or not when using POP mail accounts? ta! By the time the addin got the OnConnect event Outlook would already be online or offline. -- Ken Slovak [MVP - Outlook] Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options "Gordon" <> wrote in message news:%23fABG7TzGHA.2300@TK2MSFTNGP05.phx.gbl... > Is there an add-in for...

in excel useing comments how do you add clip art to comments?
in excel useing comments how do you add clip art to comments? Visit Debra Dalgleish's site: dhouse wrote: > > in excel useing comments how do you add clip art to comments? -- Dave Peterson Hi, For more information on comments, refer to Debra Dalgliesh's site: Challa Prabhu "dhouse" wrote: > in excel useing comments how do you add clip art to comments? ...

Possible to create user-level security in Access 2007 format w/VBA
There is no doubt that "user-level security" is not supported any longer, with the Access 2007 file format (.accdb). See So, what I am looking for is if anyone has created, or knows of, a guide to replicating "user-level security" via VBA within the new Access 2007 file format, or if it is even possible. Can anybody help me? I went with a solution that uses 3 tables to manage what person does and does not have access to. tblMenuFunctions - List of menu fuctions tblUserProfi...

Add vendor balance to "Payables Transaction Inquiry
When doing Inquiry > Purchasing > TRX by vendor, show the outstanding balance due the vendor when a vendor is selected. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane.

Excel 2003 Calendar Pop-Up Add-In
Does anyone have a calendar pop-up add-in for Excel 2003 which facilitates entry of dates into a cell? Hi subseaguy See -- Regards Ron de Bruin "subseaguy" <> wrote in message > Does anyone have a calendar pop-up add-in for Excel 2003 which facilitates > entry of dates into a cell? Ron, Thanks.. that did the trick. subseaguy "Ron de Bruin" wrote: > Hi subseaguy > > See > http://...

Formula in Vba code help
HI, I'm turning to the forum once again for help. I'm trying to write a formula in VBA, and I think it may be a sequence of where I need to place the " " " with in the code. This is what the formula looks like in the work sheet =(S14="Filled") And this is the line of code I'm trying to create CoNOws.Range("Y" & COlrow + 3).Formula = "=( ""S" & COlrow & "=""Filled"")" Any help would be appreciated. Try it this way... CoNOws.Range("Y" & COlrow + 3).Formula ...