macrobuttons ans spaces

[IkEcht]

Hi,

in my document loads of macrobuttons get replaced by text, taken from excel. A typical sub behind a macrobutton would look like this:

Sub oponthoudoordeel()
xl.Sheets("oponthoud").Select
xl.Range("b7").Select
xl.Selection.Copy
Selection.PasteSpecial DataType:=wdPasteText
End Sub

Where the cell pointed at contains a formula that returns a string. So as far as I understand, and most of the subs work fine, the resulting text-string of the formula in excel is copied.

No problem thus far but then again, in some cases the copied string is accompanied by spaces either in front or behind the string (or both). I can not yet link this behaviour to anything, nor in word nor in excel. Does anybody know about this problem and a solution?

Would be a great help as these spaces are a big bother indeed.

thanks!

[Nelviticus]

You could trim spaces from the clipboard contents then use TypeText instead of PasteSpecial:
Public Sub ClipboardTest()

Dim myData As DataObject
Dim myText As String

Set myData = New DataObject

myData.GetFromClipboard
myText = myData.GetText(1)
myText = Trim(myText)

Selection.TypeText (myText)

End Sub

[IkEcht]

Which collection are you taking the DataObject from? It doesn't recognize the type when I try to compile.

[IkEcht]

And next to looking for a workaround (I can probably think of one) I'm curious where do the spaces hail? As they are not there in most of the cases but then again there in some others. Is it a data-type in excell, a vba-type of inaccuracy, what to do, to make sure it won't happen to me anymore?

[IkEcht]

it's even easier then thought (now I only have to replace things in many macros by hand as they all point to different cells) but:


Selection.TypeText (xl.Sheets("omrijdfactor").Range("b7"))

does the trick, no spaces anymore even without trimming.
Now there are only two questions left, where did the spaces hail from and why was my predecessor using the other method (might be a pretty good reason for it that I will discover when doing the replacing allready mentioned).

[Cosmo]

it's even easier then thought (now I only have to replace things in many macros by hand as they all point to different cells) but:


Selection.TypeText (xl.Sheets("omrijdfactor").Range("b7"))

does the trick, no spaces anymore even without trimming.
Now there are only two questions left, where did the spaces hail from and why was my predecessor using the other method (might be a pretty good reason for it that I will discover when doing the replacing allready mentioned).
Don't know where the spaces were coming from, but the original code looks like it was from a recorded macro, which is often inefficient. I doubt there was a reason other than that for it to be used.