Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.basic.visual.misc > #1732
| 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 | Re: VB6 excel automation breaking |
| Date | Mon, 04 Mar 2013 11:49:32 -0000 |
| Organization | SpamTrap |
| Lines | 38 |
| Message-ID | <VA.00004312.7c6a83ec@ukrm.net> (permalink) |
| References | <VA.0000430d.76d9a785@ukrm.net> <kh1f0l$62f$1@dont-email.me> |
| Mime-Version | 1.0 |
| Content-Type | text/plain; charset=iso-8859-1 |
| Content-Transfer-Encoding | 8bit |
| X-Trace | individual.net 4cdtQRvwrDJhNRufXt/xJwp5BnQ5mEmDmycjG9YcwByYwsdUG9lTYpsgF/e3RyJje0 |
| Cancel-Lock | sha1:bjxeklcKE8QHvQAVYX8UfriO0gk= |
| X-Newsreader | Virtual Access Open Source http://www.virtual-access.org/ |
| Xref | csiph.com comp.lang.basic.visual.misc:1732 |
Show key headers only | 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
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