Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Chart Axis Text 2

Status
Not open for further replies.
JohnCR

JohnCR

Technical User
Jun 4, 2004
39
0
0
US
I'm trying to label a chart axis with this ...

Set objAxis = xlapp.ActiveChart.Axes(xlValue, xlPrimary)
With objAxis
.HasTitle = True
.Title.Text = "Issues"
End With


And I keep getting a Unable to get the Axis property of the Chart class, Code 800A03EC.

Does anyone haev any idea what I'm missing here?

TIA,

JohnCR
 
Sort by date Sort by votes
JohnCR,

In Excel, is your CHART an EMBEDDED CHART or a CHART SHEET?

Your code assumes the latter. The Location Property or Parent Property will indicate which.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
I believe a "CHART SHEET". Here's everything I have at the moment for the chart ...

Set xlapp = createobject("Excel.Application")
xlapp.Application.Visible = False
Set wb = xlapp.workbooks.add
Set objsheet1 = xlapp.Worksheets(1)
objsheet1.Name = "Data"
For Each strDate In oDic.Keys()
xlstrCount(t) = oDic.item(strDate)
xlapp.activesheet.cells(intJ+1, intI) = xlstrCount(t)
xlapp.activesheet.cells(intJ, intI) = strDate
t = t + 1
intI = intI + 1
Next
'MsgBox intI


' Create line chart
xlapp.Charts.Add
With xlapp.ActiveChart
' Possible Types of Graphs
' 95-97, 104-107, 4, 5, 65-67, -4101 >> USE TYPE 65 For LINE CHART<<
.ChartType = 65
.HasTitle = True
.ChartTitle.Text = "Project Status"
.SetSourceData xlapp.Sheets("Data").Range("A1:G2")', PlotBy = xlColumns
.Location 1
.HasDataTable = False
.HasLegend = False
End With
'Set objAxis = xlapp.ActiveChart.Axes(xlValue)
>> With xlapp.ActiveChart.Axes(xlValue) <<
.Axis(xlCategory).HasTitle = True
With .AxisTitle
.Caption = "Issues"
.Font.Name = "bookman"
.Font.Size = 10
.Characters(10, 8).Font.Italic = True
End With

This produces a chart, I just can't get the axis label on there. I can't seem to get passed the >> << line.
 
Upvote 0 Downvote
Code: 
   xlApp.Charts.Add
   With ActiveChart
     .ChartType = 65
     .HasTitle = True
     .ChartTitle.Text = "Project Status"
     .SetSourceData [Query_from_Excel_Files] ', PlotBy = xlColumns
     .Location 1
     .HasDataTable = False
     .HasLegend = False

      With .Axes(xlCategory)
         .HasTitle = True
         With .AxisTitle
           .Caption = "Issues"
           .Font.Name = "bookman"
           .Font.Size = 10
           .Characters(10, 8).Font.Italic = True
          End With
      End With
   End With
:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
hm, it doesn't like the 'SetSourceData' line.
 
Upvote 0 Downvote
I left this is ..

[Query_from_Excel_Files]

Should it be replaced with something else?
 
Upvote 0 Downvote
Sorry, that was my named range for a data source -- use your own.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
LOL, ok, that's what I thought.

Duh, I just caught the missing 'End With'
 
Upvote 0 Downvote
I can get passed the line if I remove the (xlCategory) from the With .Axes. But then that results in another error "Object Doesn't supprt this property or method: 'HasTitle'
 
Upvote 0 Downvote
All the With...End With statements matched and this code segment ran to conclusion.

What did you change?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
This is what I have ..

' Create Excel object and first chart
t = 0
intI = 1
intJ = 1
Dim xlstrCount(25)
Dim xlapp
Dim wb
Dim objsheet1
Dim objChart1
Set xlapp = createobject("Excel.Application")
xlapp.Application.Visible = False
Set wb = xlapp.workbooks.add
Set objsheet1 = xlapp.Worksheets(1)
objsheet1.Name = "Data"
For Each strDate In oDic.Keys()
xlstrCount(t) = oDic.item(strDate)
xlapp.activesheet.cells(intJ+1, intI) = xlstrCount(t)
xlapp.activesheet.cells(intJ, intI) = strDate
t = t + 1
intI = intI + 1
Next
'MsgBox intI

xlapp.Charts.Add
With xlapp.ActiveChart
.ChartType = 65
.HasTitle = True
.ChartTitle.Text = "Project Status"
.SetSourceData xlapp.Sheets("Data").Range("A1:G2") ', PlotBy = xlColumns
.Location 1
.HasDataTable = False
.HasLegend = False

With .Axes(xlCategory)
With .AxisTitle
.HasTitle = True
.Title.Caption = "Issues"
.Font.Name = "bookman"
.Font.Size = 10
.Characters(10, 8).Font.Italic = True
End With
End With
End With
 
Upvote 0 Downvote
Code: 
      With .Axes(xlCategory)
         .HasTitle = True
         With .AxisTitle
           .Caption = "Issues"
           .Font.Name = "bookman"
           .Font.Size = 10
           .Characters(10, 8).Font.Italic = True
          End With
      End With
:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
hmm, I'm not sure how you are getting this to work. I keep getting flagged on the 'With .Axes(xlCategory)' line. This only way I get passed it is to remove the '(xlCategory)'.
 
Upvote 0 Downvote
John,

I am at a loss too! The ONLY thing that I have CHANGED is the DATA SOURCE (as you discovered above) and the With .Axes(xlCategory) code I just posted.
Code: 
xlapp.Charts.Add
   With xlapp.ActiveChart
     .ChartType = 65
     .HasTitle = True
     .ChartTitle.Text = "Project Status"
     .SetSourceData xlapp.Sheets("Data").Range("A1:G2"), PlotBy:=xlColumns
     .Location 1
     .HasDataTable = False
     .HasLegend = False

      With .Axes(xlCategory)[b]
         .HasTitle = True[/b]
         With .AxisTitle[b]
           .Caption[/b] = "Issues"
           .Font.Name = "bookman"
           .Font.Size = 10
           .Characters(10, 8).Font.Italic = True
          End With
      End With
   End With


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
The version of Excel maybe? I have Office 2003. But looking in the MS Visual Basic editor it appears I'm entering it correctly.

Odd
 
Upvote 0 Downvote
I have MS OFfice 2003 also. The difference, I am running the code from Excel 2003.

Let me try from another application.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
Have you tried to replace all the xl??? constants by their values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
mm, not sure what you mean. I'm not sure what the constant for 'xlCategory' would be
 
Upvote 0 Downvote
Have you set a reference to the Excel x.x Object Library?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244
 
Upvote 0 Downvote
I'm going to say no. Everything dealing with Excel is listed above.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bdwilcox
  • Locked
  • Question
Validate that HTA text input boxes aren't blank on submission
Replies
1
Views
108
bdwilcox
bdwilcox
PiotrMP006
  • Locked
  • Question
How to quickly check if a file has 1 or more lines of text?
Replies
1
Views
102
combo
combo
Nu2Java
  • Locked
  • Question
vbscript remove line from text file if duplicate found
Replies
14
Views
219
Nu2Java
Nu2Java
Daves1
  • Locked
  • Question
VBs code Export Chart from excel (sharepoint)
Replies
16
Views
89
strongm
strongm
Don Child
  • Locked
  • Question
Deleting an arbitrary number of lines from a large text file 1
Replies
3
Views
63
Don Child
Don Child

Part and Inventory Search

Sponsor

Back
Top