Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Formatting | Excel Discussion (Misc queries) | |||
VBA paste with formatting? | Excel Discussion (Misc queries) | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
Excel paste formatting | Excel Worksheet Functions | |||
Formatting not retained using paste all and paste link | Excel Worksheet Functions |