Excel VBA values

Posted on 2004-11-04
Last Modified: 2008-01-09
OK I need the .gif output from this script to be NOT the chart Title but a cell value...
What the heck is the way to do this...


This is a snippet for the gif output
    ThisImage = "C:\test05\" & ActiveChart.ChartTitle.Text & ".gif"
         chtChart.Export Filename:=ThisImage, FilterName:="GIF"

I need the values from

=Sheet1!R & i & C2

as it cycles through the for i

This is the whole script.

Sub AddChart()
Dim chtChart As Chart
For i = 2 To 255
Set chtChart = Charts.Add
With chtChart

    ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C" & i & ":K" & i), PlotBy:= _

    ActiveChart.SeriesCollection(1).Name = "=""African American"""
 ActiveChart.SeriesCollection(1).HasDataLabels = True
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R" & i & "C12:R" & i & "C20"
    ActiveChart.SeriesCollection(2).Name = "=""Hispanic"""
 ActiveChart.SeriesCollection(2).HasDataLabels = True
      ActiveChart.SeriesCollection(3).Values = "=Sheet1!R" & i & "C21:R" & i & "C29"
    ActiveChart.SeriesCollection(3).Name = "=""White"""
       ActiveChart.SeriesCollection(3).HasDataLabels = True

      ActiveChart.SeriesCollection(4).Values = "=Sheet1!R" & i & "C30:R" & i & "C38"
    ActiveChart.SeriesCollection(4).Name = "=""Total"""
     ActiveChart.SeriesCollection(4).HasDataLabels = True
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R259C3:R259C11"
    'ActiveChart.Location Where:=xlLocationAsNewSheet

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "=Sheet1!R" & i & "C2"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
            .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.HasDataTable = True
    ActiveChart.DataTable.ShowLegendKey = True
       ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
    End With
    ThisImage = "C:\test05\" & ActiveChart.ChartTitle.Text & ".gif"
         chtChart.Export Filename:=ThisImage, FilterName:="GIF"
Next i

End Sub
Question by:hbojorquezgeek
    1 Comment
    LVL 50

    Accepted Solution

    You can make a text string using ranges such as below, there is a test that A1 contains a value



        If Range("a1") <> "" Then
            ThisImage = "C:\test05\" & Range("a1") & ".gif"
            ThisImage = "C:\test05\Blank.gif"
        End If
        chtChartC.Export Filename:=ThisImage, FilterName:="GIF"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
    This is an explanation of a simple data model to help parse a JSON feed
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now