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

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

VB6 excel automation breaking

Show all headers | 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