Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Select and copy just Cell Content (not the Cell itself) by VB macro


Reply
 
Thread Tools Display Modes
  #1  
Old 10-12-2003, 04:20 AM
CuCuBe CuCuBe is offline
Newcomer
 
Join Date: Oct 2003
Location: Czech Rep.
Posts: 3
Question Select and copy just Cell Content (not the Cell itself) by VB macro


Hi,
my problem is that I can select the content of Cell in Excel sheet [by doubleclicking (or F2) onto the Cell and simply select the whole text inside] and copy it as text.
But I cannot do this by macro.

Is there any chance to select content and copy it by VB macro?
Reply With Quote
  #2  
Old 10-12-2003, 05:28 AM
tboltfrank tboltfrank is offline
Senior Contributor
 
Join Date: Jul 2003
Posts: 1,022
Default

Are you trying to enter the active cells text, so that you have a cursor for editing, as F2, or double clicking does. Thats what you started out describing, but when you through in the word copy, I became unsure. - Please explain again.

If you want to copy your active cells text, to another location, you don't need to select your text that way, even if it is just a certain portion of the text, that you want to copy. Could I ask you to give a piece of sample data from the activecell (as in all of the text that might be contained there in), and any special result that you want, (if any), in the destination cell. That would include things like just copying part of the text in the activecell, or converting a date or number to text, etc.

Last edited by tboltfrank; 10-12-2003 at 05:39 AM.
Reply With Quote
  #3  
Old 10-12-2003, 05:50 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Record a macro while doing what you want to achieve and "Bingo"...
there you go.
Reply With Quote
  #4  
Old 10-12-2003, 06:25 AM
mwilding's Avatar
mwilding mwilding is offline
Junior Contributor
 
Join Date: May 2003
Location: Thunder Bay, Ontario
Posts: 281
Default

CuCuBe

What you are wanting to accomplish can be done using the
Code:
Selection.PasteSpecial Paste:=xlValues

Mike
Reply With Quote
  #5  
Old 10-12-2003, 07:23 AM
Poseilus Poseilus is offline
Privileges Suspended
 
Join Date: Oct 2003
Posts: 22
Default

range("B1").Value=range("K1").Value
Reply With Quote
  #6  
Old 10-13-2003, 12:15 AM
CuCuBe CuCuBe is offline
Newcomer
 
Join Date: Oct 2003
Location: Czech Rep.
Posts: 3
Wink

Quote:
Originally Posted by CuCuBe
Hi,
my problem is that I can select the content of Cell in Excel sheet [by doubleclicking (or F2) onto the Cell and simply select the whole text inside] and copy it as text.
But I cannot do this by macro.

Is there any chance to select content and copy it by VB macro?



I know about the possibility of pasting by special way and I also can make a new macro by recording while doing what I want
BUT this won't help me
the only thing I want is to have the content of Cell in the Clipboard (not the Cell but its content!!).
Is this possible to do by a macro?
Reply With Quote
  #7  
Old 10-13-2003, 01:58 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Aha,your question is a little better explained now.
Can i assume you want to copy the cell (value only) to an other application ?
Not shure it is possible to get only the cell value into the clipboard but
as a workaround you could use a textbox to store the cell value in
before you copy to the clipboard.
Check out the VBA help files for "DataObject" if you want to manipulate the clipboard.
Reply With Quote
  #8  
Old 10-13-2003, 08:13 AM
CuCuBe CuCuBe is offline
Newcomer
 
Join Date: Oct 2003
Location: Czech Rep.
Posts: 3
Thumbs up

Quote:
Originally Posted by italkid

Check out the VBA help files for "DataObject" if you want to manipulate the clipboard.


Thanks,
DataObject helped me

cucube
Reply With Quote
  #9  
Old 10-14-2003, 03:15 AM
tboltfrank tboltfrank is offline
Senior Contributor
 
Join Date: Jul 2003
Posts: 1,022
Default

Thanks Italkid,

Once again, I'm amazed at what you've managed to pull out of your magic bag

From the information that you gave about looking into the DataObject, in the Help files, here’s some code from a section of the related Help topics that I came across. I only added the source of the TextBox's value, as the ActiveCell Value and used a sheet TextBox, instead of a UserForm.. - I would have NEVER had any idea of the existance of the DataObject, or where to look, without you suggesting it

With the aid of a single Sheet TextBox, this routine copied a cell value to the clipboard and let me paste it to Word, without the cell.
Code:
Sub Clipboard_Value_WOutCell() Dim MyData As DataObject Set MyData = New DataObject ActiveSheet.TextBox1.Value = ActiveCell.Value MyData.SetText ActiveSheet.TextBox1.Text MyData.PutInClipboard End Sub
I have a use for doing this with a random Range and quantity of cells. I have two idea's for possibly putting together some code to do that. The first, would be to loop thru the Range and add all the values together (with a space in-between each one), then dump the accumulation into the TextBox. - Or I think I could manage writing a formula that concatenates them together, in much the same way, but I hope that I don't get stuck on the variable Range aspect of it. - I'll report back on what I come up with, within the next day. - Unless I see that someone else does it first.

Last edited by tboltfrank; 10-14-2003 at 03:27 AM.
Reply With Quote
  #10  
Old 10-14-2003, 10:36 AM
tboltfrank tboltfrank is offline
Senior Contributor
 
Join Date: Jul 2003
Posts: 1,022
Default

Ahh, no textbox needed. - Thought I tried this, but I must not have.
Code:
Sub ClipboardValueWOutCell() Dim MyData As DataObject Set MyData = New DataObject MyData.SetText ActiveCell.Value MyData.PutInClipboard End Sub
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro copy and paste into 2 different paragraphs mojopollo Word, PowerPoint, Outlook, and Other Office Products 1 10-12-2003 06:32 AM
Copy, change spreadsheet, move down one cell and paste internationally Word, PowerPoint, Outlook, and Other Office Products 2 08-28-2002 02:14 PM
Hekp Importing Excel JonM Word, PowerPoint, Outlook, and Other Office Products 2 05-01-2002 09:45 AM
Copy, Paste, Select All in Outlook VBA? hongman Word, PowerPoint, Outlook, and Other Office Products 0 03-06-2002 09:13 PM
How Can I select all text and copy it in a textbox iceburg General 16 10-14-2001 05:06 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->