Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info)

[giancarp]

Hi all,

I'm having a problem with Excel and I haven't been able to find a
solution.

What I'm doing is copying some data from a hidden worksheet using VBA.
This is done very easily. The problem is that Excel copies, together
to the data, also the state of the worksheet (Visible/Hidden/
VeryHidden), and this is causing me headaches. Let me give you the
algorithm to reproduce my problem.

1. Open two indipendent session of Excel. With my current settings it
opens it with three worksheets: Sheet1, Sheet2 and Sheet3.
2. On the first session type a number in cell Sheet1!A1 (for example
878)
3. Press Alt-F11to enter the VBA Editor and change the property
"Visible" for Sheet1 to be xlSheetVeryHidden
4. From the Immediate window type: Sheet1.Range("A1").Copy (this will
copy the content of cell A1 into the clipboard. Unfortunately it
copies something more than that).
5. Go on the second session of Excel and press Ctrl^V (or Paste from
the Edit menu).
6. On the second session of Excel click on the tab of the second
worksheet (Sheet2). You will see that Sheet1 has disappeared.
7. Open Visual Basic Editor on the second session of Excel, and if you
check, the Sheet1 still exists but it is VeryHidden.

The same happens if the source sheet is only Hidden, and it suggests
to me that Sheet1.Range("A1").copy doesn't only copy information about
the current cell, but also other information about the worksheet and
this is where it gets nasty for me. My application generates some data
in a hidden worksheet that the user can export by copying it to the
clipboard (using a button provided). If at that stage the user copies
the data to another session of Excel (which is quite a reasonable
thing to do), the worksheet disappears and the user panics thinking
that all this work has been lost.

And unfortunately, since after the copy everything is in the user's
hands, I cannot force any defensive action at the time the data is
copied (e.g. PasteSpecial->Values), but I need to find a work-around
to the "sheet1.range("A1").copy.

Interestingly enough, this only happens if the data is copied from one
session of Excel to another. Copying and pasting "from and to" the
same workbook or "from and to" two different workbooks open in the
same Excel session works normally as expected.

Any ideas? thank you very much
gc

 

[Nigel]

I used xl2007 (SP1) and it only copied the value, no sheets attributes were
copied, none were hidden!

I have version xl2003 SP3 on another machine I will try it on that.

 

[Susan]

can you try a simple work around - add


unhide sheet

do you thing

hide sheet


then it won't copy the hidden attribute, since it won't BE hidden @
the time.
just a simple idea.

susan

 

[giancarp]

can you try a simple work around - add

unhide sheet

do you thing

hide sheet

then it won't copy the hidden attribute, since it won't BE hidden @
the time.
just a simple idea.

susan

Hi Susan, thanks for your comment. I tried and things become even more
esoteric.
I tried the same test, but instead of calling sheet1.range("A1").copy
I'm doing:

sheet1.visible = xlSheetVisible
sheet1.Range("A1").Copy
sheet1.Visible = xlSheetVeryHidden

And the problem is still the same. Puzzled, I simplified the test and:
1. Type a number in the (visible) Sheet1!A1
2. Go to VBA and type:
sheet1.Range("A1").Copy
sheet1.Visible = xlSheetVeryHidden
3. Paste into the second session of Excel

And the spreadsheet I copied the data to becomes invisible! So it
isn't that the spreadsheet i copy from is hidden, but Excel seem to
remember that I made the sheet invisible after the copy operation, and
for arcane reasons it signals it to the other session of Excel...

Am i going nuts?
thanks
gc

 

[Susan]

like you said, somehow excel seems to be remembering the
xlveryhidden. or maybe it has something to do with using 2 sessions
of excel..............
why don't you try searching the newsgroup - maybe somebody else has
had a similar problem & found a solution??? sorry, i don't have one.

susan

 

[Dave Peterson]

I did this with two sheets in the sending workbook (sheet1 was hidden) and only
1 sheet in the receiving workbook.

Things got uglier.

That single sheet in the receiving workbook was half hidden and half visible
(kind of). The sheet tabs disappeared. I opened the VBE in the second
instance. Then I went back to excel.

The VBE "bled" into the worksheet window. But I couldn't see anything on the
worksheet grid--but if I changed selection, I could see the outline of the
active cell.

I added another worksheet to the second workbook and the sheet tabs showed
up--but sheet1 wasn't visible.

So I have a question for you.

Are you pasting just the value or do you want to include formatting?

If you're pasting just values, your code could use the windows clipboard.

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myVal As Variant

Set MyDataObj = New DataObject

myVal = Sheet1.Range("A1").Value
MyDataObj.SetText myVal
MyDataObj.PutInClipboard

End Sub

You'll have to have a reference to "Microsoft Forms 2.0 Object Library" to make
this work (tools|References within the VBE).

You'll want to read Chip Pearson's notes on working with the clipboard:
http://www.cpearson.com/excel/clipboar.htm

Then things _seemed_ to work ok.

 

[Dave Peterson]

ps. The problem occurred when I used xl2003 and winXP home.

It didn't occur when I used xl2007 and winXP Pro.

 

[giancarp]

So I have a question for you.

Are you pasting just the value or do you want to include formatting?

If you're pasting just values, your code could use the windows clipboard.

Option Explicit
Sub testme()

    Dim MyDataObj As DataObject
    Dim myVal As Variant

    Set MyDataObj = New DataObject

    myVal = Sheet1.Range("A1").Value
    MyDataObj.SetText myVal
    MyDataObj.PutInClipboard

End Sub

Thanks Dave, I didn't know about the DataObject, so it was a useful
post for me.
I am happy to get the values only, but my example was simplified: in
the real application, I need to copy a table from the spreadsheet, so
"myVal" would end up being an array of variants. It seems to me that
MyDataObj.SetText can only work on strings. CPearson's website was
also useful, but didn't mention the case of copying anything different
from a string. Is that be possible?

In any case, thanks for your reply. BTW my system is: "Windows XP
Professional" and Excel 2003 SP2
gc

 

[Dave Peterson]

I've only used dataobject to copy single values.

How about a different approach?

You tell the user to select the destination cell, then do the rest in code.
Open the sending workbook (in readonly mode) in the same instance, do the
copy|paste and then close the sending workbook.

If this doesn't work for you and you don't get a better answer here, you may
want to ask in a VB group. Maybe some of those users are more familiar with
dataobject????