We help IT Professionals succeed at work.

Changing a Header/Title in Excel Programmatically from Access

TomAsimos
TomAsimos asked
on
696 Views
Last Modified: 2010-08-05
I am using an Access application to insert data into an Excel spreadsheet.  I then use Excel's built in graphs to make a pie graph on a separate worksheet.  That works fine.  The problem is that the client wants the date range (which he has entered into text boxes in Access) to be shown on the graph.  I don't know how to make "labels" on a chart worksheet like in Access so I thought I would put this info into the header.  Problem is that I don't know the code to programmatically change the header using VBA in access.  Any clues?
Comment
Watch Question

RayDirector of Software Development
CERTIFIED EXPERT

Commented:
You can use the code below to loop through each worksheet to change the chart title.

 For Each xlsheet In xlbook.Worksheets
        For Each cht In xlsheet.ChartObjects
            If cht.Chart.HasTitle = True Then
                cht.Chart.ChartTitle.Caption = "New Title"
            End If
        Next cht
 Next xlsheet

Commented:
Heres the full code and examples on how to change the chart title from ACCESS.

CHANGE ALL CHARTS, SAVE & CLOSE
'-----------------------------------------------------
 Dim xlApp As Object
 Set xlApp = GetObject("C:\Filename.xls")
 xlApp.Application.Visible = True
 xlApp.Windows(1).Visible = True
 For Each xlsheet In xlApp.Worksheets
    For Each cht In xlsheet.ChartObjects
        If cht.Chart.HasTitle = True Then
            cht.Chart.ChartTitle.Caption = "New Title"
        End If
    Next cht
 Next xlsheet
 xlApp.Save
 xlApp.Application.Quit
 Set xlApp = Nothing
'-----------------------------------------------------

This will change all chart titles in all worksheets in the file C:\Filename.xls, save it, then exit.  If you don't want to save and exit then comment out the last 3 lines.  Post if you need specific examples to rename a single chart title.

Author

Commented:
I need specific examples to rename a single chart title.  There is only one chart per workbook.

RayDirector of Software Development
CERTIFIED EXPERT

Commented:
Rename a single chart title with one chart per workbook.

Set xlsheet = xlbook.Worksheets("Sheet1")
xlsheet.Select
Set chart = xlsheet.ChartObjects(1).Chart
chart.ChartTitle.Caption = "New Title"


Author

Commented:
The first line translated into my code is

Set objResultsSheet = objXLBook.Worksheets("TNPA Graph")

But I get the error subscript out of range, which is generally an error I get when the name of the worksheet is not spelled correctly.  It IS spelled correctly.  I even changed the name of it in Access and Excel.  Still same error.

Commented:
Try using this... It is the full code which should follow your structure:

Dim objXLBook As Object
Dim objResultsSheet As Object
Dim objChart As Object
Set objXLBook = GetObject("C:\Filename.xls")
objXLBook.Application.Visible = True
objXLBook.Windows(1).Visible = True
Set objResultsSheet = objXLBook.Worksheets("TNPA Graph")
objResultsSheet.Select
Set objChart = objResultsSheet.ChartObjects(1).Chart
objChart.ChartTitle.Caption = "My New Title"
objXLBook.Save
objXLBook.Application.Quit
Set objXLBook = Nothing

Author

Commented:
Still the same error.  Does this have something to do with the fact that when I used the Chart Wizard, I chose for this chart to be its own sheet?

Author

Commented:
That has to be the problem.  When the chart is put on an existing worksheet, the code works.

Commented:
Here you go:

-----------------------------------------------
Dim objXLBook As Object
Dim objChartsSheet As Object
Set objXLBook = GetObject("C:\Filename.xls")
objXLBook.Application.Visible = True
objXLBook.Windows(1).Visible = True
Set objChartsSheet = objXLBook.Charts("TNPA Graph")
objChartsSheet.Select
objXLBook.ActiveChart.ChartTitle.Caption = "My New Title"
objXLBook.Save
objXLBook.Application.Quit
Set objXLBook = Nothing
-----------------------------------------------

Enjoy!
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.