Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1732

Re: VB6 excel automation breaking

Show all headers | View raw

Gs spoke:
> Stuart's suggestion should work for your stated problem, I see another 
> problem you might want to change your approach on.
> 
> >    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.

-- 
Rob Pearson

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

VB6 excel automation breaking R C Nesbit <spam@ukrm.net> - 2013-03-03 09:53 +0000
  Re: VB6 excel automation breaking "Stuart McCall" <smccall@myunrealbox.com> - 2013-03-03 20:20 +0000
  Re: VB6 excel automation breaking GS <gs@somewhere.net> - 2013-03-04 01:31 -0500
    Re: VB6 excel automation breaking R C Nesbit <spam@ukrm.net> - 2013-03-04 11:49 +0000
      Re: VB6 excel automation breaking GS <gs@somewhere.net> - 2013-03-04 10:34 -0500
        Re: VB6 excel automation breaking GS <gs@somewhere.net> - 2013-03-06 12:56 -0500

csiph-web