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

VB6 excel automation breaking

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!nuzba.szn.dk!pnx.dk!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From	R C Nesbit <spam@ukrm.net>
Newsgroups	comp.lang.basic.visual.misc
Subject	VB6 excel automation breaking
Date	Sun, 03 Mar 2013 09:53:12 -0000
Organization	SpamTrap
Lines	39
Message-ID	<VA.0000430d.76d9a785@ukrm.net> (permalink)
Mime-Version	1.0
Content-Type	text/plain; charset=iso-8859-1
Content-Transfer-Encoding	8bit
X-Trace	individual.net t+TLaj70LtI3JYpXj8G6/w6wr4mB8wTFJnX+WNupmgyha5S87pV5mO1T2flRNo7l+f
Cancel-Lock	sha1:rjhQZC82xR2Dvv333pJSbha3A58=
X-Newsreader	Virtual Access Open Source http://www.virtual-access.org/
Xref	csiph.com comp.lang.basic.visual.misc:1729

Show key headers only | View raw

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

Back to comp.lang.basic.visual.misc | Previous | Next — 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