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.

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

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy



"BilalD" wrote in message
...
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.

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

I tried counting sheets as well as the period.

But that didnt have any affect on the issue.

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.

--
Regards,
Tom Ogilvy


"BilalD" wrote in message
...

I tried counting sheets as well as the period.

But that didnt have any affect on the issue.

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.

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.

--
Regards,
Tom Ogilvy

"BilalD" wrote in message
...
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.

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.

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]" wrote:

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.

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.

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

In article ,
am says...
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.