Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info)
ps. The problem occurred when I used xl2003 and winXP home.
It didn't occur when I used xl2007 and winXP Pro. Dave Peterson wrote: 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. wrote: 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 -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info)
On 31 Jan, 15:39, Dave Peterson wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info)
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???? wrote: On 31 Jan, 15:39, Dave Peterson wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copied info from Excel worksheet, but pasted info won't work in fo | Excel Discussion (Misc queries) | |||
Problem copying from a hidden worksheet (data is copied, but alsoadditional sheet info) | Excel Programming | |||
how to update a worksheet and have data copied to another sheet? | Excel Worksheet Functions | |||
Copied empty cells, give sorting problem in Data sheet | Excel Programming | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming |