.usedrange in closed workbook

[Ken McLennan]

[This followup was posted to microsoft.public.excel.programming and a copy was sent to the cited author.]

G'day there One & All,

I'm currently experimenting with a couple of different methods to
import data from various workbooks into a single workbook for various
operations to be conducted upon it.

I'm currently just copying the source worksheets with this code:

srcPg.copy after:=tgtPg

...and it's working fine. At least it was until some of my clients
started to implement their own techniques in their own workbooks (how
DARE they!!!).

I'm finding now when I cycle through my array of workbook titles,
obtained from a GetOpenFileName dialogue, open them in turn, and import
Sheet1 from each; that I get dialogues asking me about updating links,
and/or conflicting names from where different clients have built
formulae and have used the rather imaginative "Range1" in their sheets.
Hence, the first imports OK, but from there on in I get Name Conflict
errors (at least, I think that's what they were called).

I've also tried copying the UsedRange to the clipboard and then
using PasteSpecial to paste only the values and formatting, but that
wasn't quite as successful as I'd hoped. Trouble is that I can't now
remember why not. I'll have to give it another go to find out what went
wrong there.

In the meantime I thought I'd investigate the technique of leaving
the workbooks closed and forming links which I found on a developer's
site somewhere.

The problem is that I don't know the extent of the data in each
sheet that I need to copy. The data is consistently on Sheet1, and also
a consistent number of columns. However the number of rows vary.

Is there a way to determine the UsedRange from a closed workbook?
My experiments to date have all met with failure, however that's just as
likely to be my poor programming skills rather than an inability of XL
to perform the calculation.

Hope to hear back soonish
Thanks to you all
Ken McLennan
Qld, Australia

 

[Tom Ogilvy]

I don't see why PasteSpecial Values shouldn't work (unless you have merged
cells)

 

[Ron de Bruin]

Try this
http://www.rondebruin.nl/copy3.htm

Read the tips section about the workbook open arguments

 

[Ken McLennan]

[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

G'day there Tom,

I don't see why PasteSpecial Values shouldn't work (unless you have merged
cells)

I'm not sure now myself why it didn't work. I can't even recall
what messages I got. I intend to have a play with it tonight to see
what's going on.

Theoretically there shouldn't be any merged cells as the
individual worksheets all come from the same template and it's got
nothing but separate cells. However, it is quite likely that someone
could have merged some cells on their own copy.

The data I'm working with is a series of about 40 - 50 shift
rosters for the same number of sections. Each Roster Clerk has their own
ideas on how it should be done and there's little consistency. Those who
use the template "as is" (the majority) aren't causing me problems. It's
the clerks who have devised their own macros and formulae that are
creating havoc. I don't want to dictate their usage, especially as some
of their ideas are quite productive. However I also need to collate all
their results and trying to navigate around their code is a pain.

Oh well. I'm off to look again at the PasteSpecial code I've used.
I'd hazard a guess that it's more a problem of my code not handling the
data correctly once it's imported, rather than a problem with the
PasteSpecial. Still, I hope to find out shortly.

See ya
Thanks for the quick reply.
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there again, Tom,

I'm not sure now myself why it didn't work. I can't even recall
what messages I got. I intend to have a play with it tonight to see
what's going on.

I've now gone back and had a look at that version of my code.
Guess what? It wasn't a bug in Microsoft's code it was a typo on my part
(dammit!!). Actually it was a couple of instances of the same typo -
that's what happens when you mistype something and then copy & paste it.

I had this loop:

With ThisWorkbook

.Sheets.Add after:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = newName(srcBk.Name)

srcPg.UsedRange.Copy

.Sheets(.Sheets.Count).Range("A1").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
.Sheets(.Sheets.Count).Range("A1").PasteSpecial _
Paste:=xlPasteFormats
Application.CutCopyMode = False
End With

Except that where I use ".Sheets(.Sheets.Count)" I had "Sheets
(Sheets.Count)" behind the "after:=" and ".Sheets(Sheets.Count)"
everywhere else.

Now that I have periods in where they're supposed to be it works
fine.

Only thing now is that after pasting, all of the pasted range
stays selected. I've tried using range("A1").select, but without
success. The entire pasted range still remains selected. I think I may
have to select a cell outside the range, or activate the sheet first.
Any suggestions?

It's not really a biggy at the moment, but I'm sure to find a
client who'll cut rather than copy and things will stuff up from there.

See ya
Thanks for your help
Ken McLennan
Qld, Australia

 

[Ken McLennan]

G'day there One & All,

Try this
http://www.rondebruin.nl/copy3.htm

Read the tips section about the workbook open arguments

For those who were following this thread, I HAVE thanked Ron for
his assistance, but just realised that I hit Reply, and not Follow Up in
my Gravity NewsReader. Hence, you won't see my comments about what a
great link that is above. There's a stack of information on that page,
and if you follow the link to Ron's Tips Page, there are bucketloads of
helpful stuff!

I highly recommend it.

Thanks very muchly Ron,
and now Thanks Publicly too.

See ya
Ken McLennan
Qld, Australia

 

[Ron de Bruin]

You are welcome Ken

 

[Tom Ogilvy]

range("A1").Select works for me. range("A1").Activate only changes the
activecell - but select should reduce the selection.

 

[Ken McLennan]

G'day there Tom,

range("A1").Select works for me. range("A1").Activate only changes the
activecell - but select should reduce the selection.

I thought it should have worked too, and since it worked on yours
there was obviously something amiss with mine.

On checking I found that the sheets weren't active so the select
method wasn't working. Easily fixed though. Thanks for your assistance,
it's much appreciated. I *am* learning, but it's a slow process =)

See ya
Thanks again,
Ken McLennan
Qld, Australia