MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to copy a range between 2 "GetObject" workbooks

  Asked By: Bertha    Date: Nov 06    Category: MS Office    Views: 1880

This should be a simple matter, but I sure cannot get it to work.

1. I am working with 2 workbooks. Both are named in "Set =
GetObject..." statements at the start of the routine. Therefore, the
second workbook is hidden.

2. I need to copy a column of numbers from the hidden workbook to the
active workbook. The operation always gives me am "Object doesn't
support this method or property" error message at the paste step. I am
not sure, however, that the copy operation is working either although
it doesn't give me an error message.

I've tried activating the hidden workbook, selecting the sheet and
range, and everything between, plus different ways of saying "paste".
Nothing works, so my first question is if a copy/paste is possible in
this situation. I do know that I can count the number of columns in
the hidden workbook prior to selecting the one I want.



5 Answers Found

Answer #1    Answered By: Bian Nguyen     Answered On: Nov 06

If WB1 is your main wb and WB2 is your hidden  wb, and
WS2 is the sheet  in WB2 containing the data of interest,
then maybe something like this will help..

Dim aaTEMP as Variant
aaTEMP = WS2.Range("A1:A400")
b = b

assuming you want to copy  col A (natch, change as req'd).
Then, put a break at "b = b", and examine the contents
of the array aaTEMP in your locals window. If it has
successfully been populated, then you can just "dump"
it's contents into WB1.

Answer #2    Answered By: Daniel Jones     Answered On: Nov 06

Do you mean hidden, or is the workbook  just not visible (i.e. obscured)? There
is a big difference.

You don't seem to be able to copy  a hidden  sheet to the clipboard, although you
can copy selected ranges of a hidden sheet  to the clipboard. You can, of
course, copy an obscured sheet to the clipboard.

What does your code look like?

Answer #3    Answered By: Mercedes Andrews     Answered On: Nov 06

I have this problem in two directions, read and write. It's obviously
an access (small "a") problem.The problem workbook  is hidden. It comes
about this way.

WB1 is the workbook I already have open. No problem with it. In the
declarations of this workbook I set  WB1 and WB2 as object  variables (I
think my nomenclature is correct -- I use "Set WB1 = fullpathname for
first WB" and have another statement set statement for the second
workbook, which I do not open.)Therefore WB2 never "opens"; it shows
as a hidden  file in Excel's Window-->Unhide dropdown.

The main routine  in WB1 starts a loop for analyzing data that are
organized by day. WB2 contains multiple columns  of daily data. At the
beginning of every WB1 loop I go out to WB2, copy  the next column, and
paste it into a column  in WB1 for processing. This is the problem. I
cannot copy any data from WB2, even though I can use a subroutine that
goes into WB2 and counts the number  of filled columns.

I have another routine in another workbook that is trying to paste
data into the same WB2 workbook. It doesn't work  either.

So, I can do something with WB2, but cannot copy from or paste  to it.
Have searched through all my books for a solution, and nothing I've
found works. Is it possible?

I plan to try another tact, whereby I'll open WB2 directly, load a
whole sheet  into an array and then loop through the array columns, but
would really like to know if there is a solution for the first way.

Answer #4    Answered By: Fuzairah Neeman     Answered On: Nov 06

When you copy  something from one workbook  to another, you must have both
workbooks open. The following link provides information on
transferring data from one worksheet to another.


Answer #5    Answered By: Brandi Ramirez     Answered On: Nov 06

I inferred that from my experienc trying to do so, but
solved the problem by transferring the worksheet with the archived
data to an array and working  from there. This can be done without
directly opening the archive workbook. It is referenced via GetObject
and never shows as an open workbook  (it's hidden), but can be read
into the array -- all of which is fine with me. My routine  has no need
to write to the archive file.

Didn't find what you were looking for? Find more on How to copy a range between 2 "GetObject" workbooks Or get search suggestion and latest updates.