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

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

VB6 excel automation breaking

Back to article view | Back to comp.lang.basic.visual.misc

Contents

  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

#1729 — VB6 excel automation breaking

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

[toc] | [next] | [standalone]

#1730

"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)

[toc] | [prev] | [next] | [standalone]

#1731

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...

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

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#1732

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

[toc] | [prev] | [next] | [standalone]

#1733

> 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.

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.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#1734

<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.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.basic.visual.misc

csiph-web