Want To Move Excel Chart to Last Tab Using Access VBA

[JonMulder]

Greetings,

I've been struggling with this for a few hours and need to reach out.

I am creating Excel charts in Access. I have creating an Excel application object (if that's the right terminology) named as "x1". Most of the posts I've seen say to use the "Move after:=x1.Sheets.Count" method to position the Activechart to the last tab. I can't seem to get this to work (all other Excel code DOES work fine).

Here's a snippet of my code:

x1.Charts.Add
'Delete all Series.
With x1.ActiveChart
For i = .SeriesCollection.Count To 1 Step -1
.SeriesCollection(i).Delete
Next i
End With
x1.ActiveChart.Location Where:=xlLocationAsNewSheet
x1.ActiveChart.Move after:=x1.Sheets.Count
'''x1.ActiveChart.Move after:=Sheets(Sheets.Count) 'This line rem'd out.
x1.ActiveChart.ChartType = xlLineMarkers
x1.ActiveChart.HasTitle = True

Thanks for any insight, folks can provide!

Jon Mulder
Engineering Geologist
California Department of Water Resources

 

[Beetle]

I'm not clear on whether you want to move the chart to an existing sheet, or a new sheet.

To move to an existing sheet;

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet 3"

using the actual name of the appropriate sheet for the Name argument of course.

To move to a new sheet at the end, first place the chart on a new sheet;

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="NewChartSheet"

then move the Sheet (not the Chart);

Sheets("NewChartSheet").Move After:=Sheets.Count

 

[JonMulder]

Thanks for your input, Sean. And I really like your tag line!

Based on your suggestions, I tried the following code but still get the "move method of chart class failed" error:

x1.Charts.Add
x1.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=strWellName
x1.Sheets(strWellName).Move After:=x1.Sheets.Count

I opened up a "watch window" to track my "Sheets.count" and it changed from 1 to 2 on the "x1.Charts.Add" line.

BTW, x1 is an Excel application created in my Access database and strWellName is a string variable for the WellName.

 

[Beetle]

Sorry, that last line should be;

Sheets("NewChartSheet").Move After:=.Sheets(.Sheets.Count)

BTW, x1 is an Excel application created in my Access database

Right, I was aware, I was just giving you the Excel part of the code. The following (quickly bashed out) code is tested, and does work. Opens an existing workbook, selects a chart on Sheet2 and moves it to a new sheet at the end.

Dim x As Excel.Application
Dim y As Excel.Workbook

Set x = CreateObject("Excel.Application")

With x
    .Visible = True
    Set y = .workbooks.Open("C:\users\sbailey\Documents\Book2.xlsx")
    With y
        .Sheets("Sheet2").Select
        .ActiveSheet.ChartObjects("Chart 2").Activate
        .ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="NewSheet"
        .Sheets("NewSheet").Select
        .Sheets("NewSheet").Move After:=.Sheets(.Sheets.Count)
    End With
End With

This requires a reference to the Excel object library (which I believe you already have). You could do it with late binding but you would need to find the constant value for xlLocationAsNewSheet.

BTW - A good way to determine how to create the necessary code when manipulating Excel from within Access, if you aren't sure what the proper methods/functions/arguments are, is to open your Excel file (in Excel) and start recording a macro. Do whatever you need to do manually, stop recording, then look at the code that was generated by the macro. Then just duplicate this code in your Access project.

 

[JonMulder]

Thanks, Sean, for the suggestion. This is the code that finally worked for me:

'Add a sheet for MeasurementData.
x1.Worksheets.Add().Name = "WellMeasurementData"
x1.ActiveSheet.Move After:=x1.Sheets(x1.Sheets.Count)

Jonathan Mulder
Engineering Geologist
California Department of Water Resources

 

[JonMulder]

I realized my last post didn't explicitly address the issue regarding placing a chart on a sheet and then placing the sheet at the end. This is the code that worked for me for puyying the chart on the last sheet:

x1.Charts.Add
x1.ActiveChart.Location WHERE:=xlLocationAsNewSheet, Name:=strWellName
x1.ActiveSheet.Move After:=x1.Sheets(x1.Sheets.Count)

Jonathan Mulder