VB6 excel automation breaking

[R C Nesbit]

I have an app which generates a time sheet report in an
excel sheet.
I have a timesheet.xlt template, which has 2 worksheets, 1
is a summary sheet, on line per Person, the second is
called "Name"
I open a recordset containing a single field ("Person")
then loop though the recordset adding a new worksheet for
each record returned.

wsRow(1) = 4
i = 2
Do While Not rsT.EOF
oWS(1).Cells(wsRow(1), 1) = rsT.Fields(0)
oWB.Sheets("Name").Select
oWB.Sheets("Name").Copy After:=Sheets(i)
oWB.Sheets("Name").Cells(1, 2) = rsT.Fields(0)
oWB.Sheets("Name").Cells(1, 7) = sReportDate
oWB.Sheets("Name").Name = rsT.Fields(0)
oWB.Sheets("Name (2)").Name = "Name"
i = i + 1
wsRow(1) = wsRow(1) + 1
rsT.MoveNext
Loop

This works perfectly.

Once

If it is run a second time excel generates multiple errors
starting with:
1004 Method 'Sheets' of object '_Global' failed
at the line:
oWB.Sheets("Name").Copy After:=Sheets(i)

[Stuart McCall]

Post by R C Nesbit
I have an app which generates a time sheet report in an
excel sheet.
I have a timesheet.xlt template, which has 2 worksheets, 1
is a summary sheet, on line per Person, the second is
called "Name"
I open a recordset containing a single field ("Person")
then loop though the recordset adding a new worksheet for
each record returned.
wsRow(1) = 4
i = 2
Do While Not rsT.EOF
oWS(1).Cells(wsRow(1), 1) = rsT.Fields(0)
oWB.Sheets("Name").Select
oWB.Sheets("Name").Copy After:=Sheets(i)
oWB.Sheets("Name").Cells(1, 2) = rsT.Fields(0)
oWB.Sheets("Name").Cells(1, 7) = sReportDate
oWB.Sheets("Name").Name = rsT.Fields(0)
oWB.Sheets("Name (2)").Name = "Name"
i = i + 1
wsRow(1) = wsRow(1) + 1
rsT.MoveNext
Loop
This works perfectly.
Once
If it is run a second time excel generates multiple errors
1004 Method 'Sheets' of object '_Global' failed
oWB.Sheets("Name").Copy After:=Sheets(i)
--
Rob Pearson

Try:

oWB.Sheets("Name").Copy After:=oWB.Sheets(i)

[GS]

Stuart's suggestion should work for your stated problem, I see another
problem you might want to change your approach on.

Post by R C Nesbit
oWB.Sheets("Name (2)").Name = "Name"

This line is trying to rename the copied sheet to the same name as the
sheet it was a copy of. I'd expect you'd be renaming it to the one of
the names in your rsT, and keep the sheet "Name" as the template...

wsRow(1) = 4: i = 2
Do While Not rsT.EOF
oWS(1).Cells(wsRow(1), 1) = rsT.Fields(0)
'Reset counters for next record
i = i + 1: wsRow(1) = wsRow(1) + 1

'Add a copy of sheet "Name" for each name in rsT
oWB.Sheets("Name").Copy After:=oWB.Sheets(i)
'The copy is now the active sheet
With oWB.ActiveSheet
.Name = rsT.Fields(0) '//rename immediately
.Cells(1, 2) = rsT.Fields(0): .Cells(1, 7) = sReportDate
End With
rsT.MoveNext
Loop

[R C Nesbit]

Post by GS
Stuart's suggestion should work for your stated problem, I see another
problem you might want to change your approach on.

Post by R C Nesbit
oWB.Sheets("Name (2)").Name = "Name"

This line is trying to rename the copied sheet to the same name as the
sheet it was a copy of. I'd expect you'd be renaming it to the one of
the names in your rsT, and keep the sheet "Name" as the template...
'Add a copy of sheet "Name" for each name in rsT
oWB.Sheets("Name").Copy After:=oWB.Sheets(i)
'The copy is now the active sheet
With oWB.ActiveSheet
.Name = rsT.Fields(0) '//rename immediately
.Cells(1, 2) = rsT.Fields(0): .Cells(1, 7) = sReportDate
End With
rsT.MoveNext
Loop

Well thanks to both of you, the issue is resolved.

The reason for renaming the original template sheet to rsT.Fields(0) and
the new copy to Name was that the blank template was automatically moved
along and ended up as the last worksheet.
Renaming the copy leaves the blank template sheet just after the
sheet(1) summary.
fixed by adding:
oWB.Sheets("Name").Move After:=oWB.Sheets(sName)

oWB.Sheets("Name").Delete doesn't work very well as Excel pops up a
dialog for confirmation.

[GS]

Post by R C Nesbit
The reason for renaming the original template sheet to rsT.Fields(0)
and the new copy to Name was that the blank template was
automatically moved along and ended up as the last worksheet.
Renaming the copy leaves the blank template sheet just after the
sheet(1) summary.
oWB.Sheets("Name").Move After:=oWB.Sheets(sName)
oWB.Sheets("Name").Delete doesn't work very well as Excel pops up a
dialog for confirmation.

If you want to insert copies to the right of the last sheet, change
this line...

oWB.Sheets("Name").Copy After:=oWB.Sheets(i)

To

oWB.Sheets("Name").Copy After:=oWB.Sheets(oWB.Sheets.Count)

..which will preserve the startup location of "Name".


As for the Delete alert issue...

appXL.DisplayAlerts = False '//turn notification off
'delete the sheet
appXL.DisplayAlerts = True '//turn notification back on

..where 'appXL' is the object variable holding a ref to your instance
of Excel.

[GS]

<FWIW>
I did some thinking about your approach after working on one of my VB6
frontloader apps for Excel automation. (Excel is my primary dev
platform, but I use a VB6.exe frontloader for various reasons, the main
reason being so my Excel addins use their own instance of Excel)

Your comment about the Delete alert suggests you remove (or want to
remove) Sheets("Name") when you're done. Instead of deleting, you can
just hide it (no alert raised) by setting its Visible prop False.

OR

If it's a specially formatted sheet you could ship it as a separate
file and use the Sheets.Add method of the oWB object...

Dim wkbTarget As Object '//the current file to receive data
Dim wksTarget As Object '//the current name sheet to receive data

Set wkbTarget = appXL.Workbooks.Add Template:=App.Path & "\Summary.xls"
Set oWS = wkbTarget.Sheets(1)

wsRow(1) = 4: i = 2
Do While Not rsT.EOF
oWS(1).Cells(wsRow(1), 1) = rsT.Fields(0)
'Reset counters for next record
i = i + 1: wsRow(1) = wsRow(1) + 1

'Add a copy of sheet "Name" for each name in rsT
Set wksTarget = wkbTarget.Sheets.Add _
Type:=App.Path & "\Namesheet.xls", _
After:=wkbTarget.Sheets(wkbTarget.Sheets.Count)
With wksTarget
.Name = rsT.Fields(0) '//rename immediately
.Cells(1, 2) = rsT.Fields(0): .Cells(1, 7) = sReportDate
End With
rsT.MoveNext
Loop

OR

If it's just a blank sheet then you can remove the line above that
specifies the Type arg for the Sheets.Add method.

[Adrien Huvier]

Hi,

Post by R C Nesbit
oWB.Sheets("Name").Delete doesn't work very well as Excel pops up a
dialog for confirmation.

Application.DisplayAlerts=False
oWB.Sheets("Name").Delete
Application.DisplayAlerts=True