asked on

Changing a Header/Title in Excel Programmatically from Access

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?

Ray

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

elantra

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.

TomAsimos

ASKER

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

Ray

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"

TomAsimos

ASKER

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.

elantra

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

TomAsimos

ASKER

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?

TomAsimos

ASKER

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

elantra

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!

ASKER CERTIFIED SOLUTION

elantra

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial