Add a Chart as the last sheet

[BilalD]

Hello all

I am trying to add a chart as the very last sheet in a workbook. I have the
following VB 6.0 code. Using Excel 2003.

Option Explicit
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook

Dim excelSheet(0 To 3) As Excel.Worksheet
Dim excelchart(0 To 3) As Excel.Chart

Private Sub Command1_Click()

excelApp.Visible = True
Set excelBook = excelApp.Workbooks.Add
Set excelSheet(0) = excelBook.Worksheets.Add

With excelBook
..Sheets.Add After:=.Worksheets(Worksheets.Count), Type:=xlChart
End With

End Sub

What I notice is that even though I specify to add the chart after the very
last sheet, it already adds it right before the last. I didnt see anything in
the documentation that would lead me to believe that this is expect behavior.
I know I can move the chart to be the last sheet and thats how Im working
around this.

Did I just miss something? Can someone shed some light on this behavior? It
might have to do with adding sheets of different types, because I dont see
this happen if I add a WorkSheet instead of a chart.

I know im not the only one seeing this.

Thanks.

 

[Tom Ogilvy]

Your missing a period in this line

With excelBook
..Sheets.Add After:=.Worksheets(Worksheets.Count), Type:=xlChart
End With

the worksheet.count should start with a period.

Perhaps that is the problem.

another think is your counting worksheets and perhaps that is what you want,
but since you have charts as well, you might want to use Sheets instead of
worksheets

With excelBook
..Sheets.Add After:=.Sheets(.Sheets.Count), Type:=xlChart
End With

 

[BilalD]

Thanks for your response.

The period did not affect the behavior.

And I tried counting Sheets instead of worksheets and it still does the same.

With excelBook
..Sheets.Add After:=.Sheets(.Sheets.Count), Type:=xlChart
End With

 

[BilalD]

I tried counting sheets as well as the period.

But that didnt have any affect on the issue.

 

[Tom Ogilvy]

It works for everyone else. Only thing I have seen that will affect it is
hidden sheets. What method are you using to move it to the last sheet after
insertion. Isn't it the same approach?

guess you could loop through all the sheets and retain a reference to the
last sheet.

 

[BilalD]

I do the following to move the sheet to the end. Not the best way, but it
works.

Set excelchart(0) = excelBook.Sheets("Chart1")
excelchart(0).Move after:=excelBook.Sheets(excelBook.Sheets.Count)

Sheets.Count is returning the correct number of sheets. I am not doing
anything with hidden sheets, unless Excel has some hidden sheet alwasy
include. But if that were the case, then the move method would not have
worked.

 

[Tom Ogilvy]

Just like I said, you are using the exact same code as what I suggested to
determine what the last sheet is. In one case you say it doesn't work and
in this case you say it does.

Perhaps you didn't add the period and use Sheets after all. Just a guess.

 

[BilalD]

I apologize. Let me clarify.

I expected the Sheets.Add method to add the newly created chart at the end
of the list of Sheets in the work becuase I specify it to add the new sheet
After the last sheet in the workbook, which is the WorkSheet returned by
excelBook.Sheets(excelBook.Sheet.Count) .
This was never working because it adds the new chart just before the very
last sheet.

The way I got it to work was to move the newly created sheet that was
returned by the Add method to the end of the sheet list. Moving to the end
works as documented. I know about this already. I just want to know why
doesnt Add work right.

 

[Peter Huang [MSFT]]

Hi

Based on my research, so far we do need to use the Move to add the chart
after the last sheet.

Sub Test()
Application.ScreenUpdating = False ' Prevents screen refreshing.
Charts.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
Application.ScreenUpdating = True ' Enables screen refreshing.
End Sub

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tom Ogilvy]

After:=excelBook.Sheets(excelBook.Sheet.Count)
should be

After:=excelBook.Sheets(excelBook.Sheets.Count)

Maybe that is your problem - a typo. In any event, you are using the same
code in each instance to specify the last sheet.

 

[Tom Ogilvy]

Base on what Peter Huang posted, this appears to be a bug or one of those
"this is by design" and you are correct, the chart is not inserted after the
last sheet even though you use the same code in each instance to determine
the last sheet.

So I stand corrected.

 

[Tushar Mehta]

This is a bug that has existed since at least the early/mid '90s. You
have to do what you are already doing: add the sheet and then move it
to the correct location.

When needed, I add a module called ExcelFUs to an add-in. A
'AddAtEnd' function is a standard component of such a module.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[BilalD]

Thanks for your help everyone. It would be nice to have this documented. It's
easy enough to fix (on the client side), but irritating none the less.

 

[Peter Huang [MSFT]]

Hi

Thank you for your feedback.
And if you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.