How to paste without formatting
 

Hi,

I have a macro that pastes what is in the copy buffer, and then analyses the
resultant paste. Problem is that if the copy buffer has tabs in it, then
excel will interpret this as having to paste over a variety of columns. I
just want it to ignore any characters that may take the data over multiple
columns (e.g. like tabs).

The command I am using is:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

I thought the Format of Text would ignore the tabs.

Any help is most appreciated.

Cheers,
Les Landau

How to paste without formatting
 

There is a way to paste the buffer into a variable, from there you can use
the replace method to remove tabs eg....
myvar = replace(myvar,chr(9),"')
as to how to paste the buffer into a variable, i'm not quite sure how that
is done, anybody else know?
--
When you lose your mind, you free your life.


"Les Landau" wrote:

Hi,

I have a macro that pastes what is in the copy buffer, and then analyses the
resultant paste. Problem is that if the copy buffer has tabs in it, then
excel will interpret this as having to paste over a variety of columns. I
just want it to ignore any characters that may take the data over multiple
columns (e.g. like tabs).

The command I am using is:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

I thought the Format of Text would ignore the tabs.

Any help is most appreciated.

Cheers,
Les Landau

How to paste without formatting
 

Here's an example of how to directly access the clipboard.

Declare Function OpenClipboard32 Lib "user32" Alias "OpenClipboard" _
(ByVal hwnd As Long) As Long

Declare Function GetClipboardData32 Lib "user32" _
Alias "GetClipboardData" (ByVal wFormat As Long) As Long

Declare Function GlobalLock32 Lib "Kernel32" Alias "GlobalLock" _
(ByVal hMem As Long) As Long

Declare Function lstrcpy32 Lib "Kernel32" Alias "lstrcpy" _
(ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

Declare Function GlobalUnlock32 Lib "Kernel32" Alias "GlobalUnlock" _
(ByVal hMem As Long) As Long

Declare Function CloseClipboard32 Lib "user32" Alias "CloseClipboard" () As
Long

Const CF_TEXT = 1

Sub CB_GetDataDemo() ''<<RUN THIS
ActiveCell.Value = CB_GetData
End Sub

Function CB_GetData() As String
Dim hClipMemory As Long, lpClipMemory As Long, StrBuf As String * 4096
OpenClipboard32 0
'Obtain the handle to the global memory block that is referencing the
text.
hClipMemory = GetClipboardData32(CF_TEXT)
'Lock Clipboard memory so we can reference the actual data string
lpClipMemory = GlobalLock32(hClipMemory)
On Error GoTo Done
If Not IsNull(lpClipMemory) Then
lstrcpy32 StrBuf, lpClipMemory
GlobalUnlock32 hClipMemory
End If
Done:
CloseClipboard32
CB_GetData = StrBuf
End Function


--
Jim
"ben" (remove this if mailing direct) wrote in message
...
| There is a way to paste the buffer into a variable, from there you can use
| the replace method to remove tabs eg....
| myvar = replace(myvar,chr(9),"')
| as to how to paste the buffer into a variable, i'm not quite sure how that
| is done, anybody else know?
| --
| When you lose your mind, you free your life.
|
|
| "Les Landau" wrote:
|
| Hi,
|
| I have a macro that pastes what is in the copy buffer, and then analyses
the
| resultant paste. Problem is that if the copy buffer has tabs in it, then
| excel will interpret this as having to paste over a variety of columns.
I
| just want it to ignore any characters that may take the data over
multiple
| columns (e.g. like tabs).
|
| The command I am using is:
|
| ActiveSheet.PasteSpecial Format:="Text", Link:=False,
DisplayAsIcon:=False
|
| I thought the Format of Text would ignore the tabs.
|
| Any help is most appreciated.
|
| Cheers,
| Les Landau
|
|
|

How to paste without formatting
 

ok nevermind I got it les, This code will only work with a userform in the
workbook.

Sub notabs()
Dim myd As dataobject
Set myd = New dataobject
Cells(1, 1).Copy
myd.getfromclipboard
mydat = myd.GetText(1)
mydat = Replace(mydat, Chr(9), "")
End Sub

that takes the value in A1 and removes formatting and tabs.
Ben
--
When you lose your mind, you free your life.


"ben" wrote:

There is a way to paste the buffer into a variable, from there you can use
the replace method to remove tabs eg....
myvar = replace(myvar,chr(9),"')
as to how to paste the buffer into a variable, i'm not quite sure how that
is done, anybody else know?
--
When you lose your mind, you free your life.


"Les Landau" wrote:

Hi,

I have a macro that pastes what is in the copy buffer, and then analyses the
resultant paste. Problem is that if the copy buffer has tabs in it, then
excel will interpret this as having to paste over a variety of columns. I
just want it to ignore any characters that may take the data over multiple
columns (e.g. like tabs).

The command I am using is:

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

I thought the Format of Text would ignore the tabs.

Any help is most appreciated.

Cheers,
Les Landau