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

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

Re: VB6 excel automation breaking

Show all headers | View raw

"R C Nesbit" <spam@ukrm.net> wrote in message 
news:VA.0000430d.76d9a785@ukrm.net...
>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)
>
> -- 
> Rob Pearson

Try:

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

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