A Question on Ron de Bruin's "Copy a range from closed workbook" function

[JonWestcot]

Hi all:

I've got a question regarding Ron de Bruin's function "GetRange" in the
"Copy a range from closed workbook" examples at the following link:

http://www.rondebruin.nl/copy7.htm

In the GetRange function, a statement attempts to resize a destination
range with the range of the source range. However, at the point it tries to
do this, the source range hasn't been identified. The source file isn't
even opened.

Any suggestions on how to get around this problem?

Thanks!

Jon

P.S. Sorry if this is a repost; I sent this out hours ago and it hasn't
shown up yet, so I thought I'd err on the side of caution and resend it.

 

[Ron de Bruin]

Hi Jon

The Source and destination are in this code line

GetRange "C:\Data", "test3.xls", "Sheet1", "A1:B100", _
Sheets("Sheet1").Range("A1")

1: Path Source file
2: Source File name
3: Source sheet name
4: Source range
5: Destination sheet/range

 

[JonWestcot]

Hi Roy, Ron, et al.:

Have you tried using the code and received an error? If so what error?
Have you used Ron's example codes whilst changing the relevant
variables?

Yes, I've tried the code. The error I get indicates that the Range
fails on the _Global object.

However, I believe I figured out part of my problem. I was specifying a
named range from the source file when I really needed to specify an actual
range. Once I did that, the aforementioned error went away.

But, now I have two more problems!

First, I am trying to copy a range of 8 rows, 1 column wide. The
values, in order, are:

A, B, C, , , , , X

However, when they paste, a 0 (zero) replaces the blanks. And, try as I
might, I cannot get blanks to be pasted in.

Second, I have two situations where I need to copy the entire contents
of one closed worksheet into a sheet in my open workbook. Is there a way to
specify a range generically that will let me do this?

Thanks!

Jon

 

[Ron de Bruin]

Opening the workbook with code give you much more control
and most of the time fast.

If you need help with the code post back

This page give you a start
http://www.rondebruin.nl/copy3.htm

 

[JonWestcot]

Hi Ron:

Thanks for the uber-fast reply! And thanks for the link. I'll try
switching things around to opening up the external files manually and see if
that helps.

Do you have any idea, though, why the blank cells were replaces with
zeroes?

Thanks again.

Jon

 

[Ron de Bruin]

We create formula links in this example first and then convert it to values.
That's why the zero.

You can hide zeros if you want
Tools>Options..View
See zero values