Word VBA Replace multiple Spaces in Text with BB Code String

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by StuartR »

Alan,

If you want people to respond to your posts then could I please suggest that you try to make them more concise. Few of our loungers will have the time or patience to read such a long post to try and understand whether you are asking for more help, or simply sharing your current understanding.
StuartR


User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

StuartR wrote:...could I please suggest that you try to make them more concise.....
@ StuartR
Hi Stuart
Apologies for that. I am still new to posting here.
It was intended mainly as a follow up sharing my current understanding and showing / discussing an alternative which I thought might benefit or interest anyone hitting the Thread in a search in the Future.
Sorry
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by StuartR »

No apology needed Alan. It was just advice on the best way to engage people here.
StuartR


User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

@ StuartR
StuartR wrote:.....just advice on the best way to engage people here.
Hi Stuart
OK.
I do have an unusual posting style!! Won’t make a habit of it!
Alan

P.s
I passed on the codes developed here, in this Thread
http://www.excelforum.com/the-water-coo ... ace-2.html" onclick="window.open(this.href);return false;
( and answered another Thread of mine, base on what I learned here!
http://www.excelfox.com/forum/f5/code-m ... /#post9678" onclick="window.open(this.href);return false;
_ hope that is OK
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by HansV »

Hi Alan,

It's fine to post a solution that you found with our help elsewhere, with a reference as you did.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi Hans
OK.
Thanks a lot, I learned a lot here
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by Doc.AElstein »

Hi

This is just a quick follow up
, rather than a Question, should anyone in the future be interested in the Final codes developed here last Sunday.
_.....
I noticed something strange, which caused me problems. By simple experimenting I got a “work around”

So if you use either of the two code versions developed here to

_a) Copy a post you have prepared previously in Word to the Clipboard by selecting the Text you want and running one of the programs. Then paste manually ( Ctrl V ) into a Forum Post Editor from the Clipboard. - Than no problem. ( The code ads Tidies on in place of spaces greater than one, (and adds BB Code Tags to give the Tidlies a color, light blue, which is almost invisible to the eye )
( This overcomes ( or gives the impression you have overcome ) , the Forums editor habit of eating Spaces of greater than one. )
But
b) If you first re paste from the Clipboard back into Word first, and then re copy that manually and paste manually into the Forum Editor, then sometimes carriage returns “vanish”

I Googled and experimented a lot.
The workaround I found was to replace in the final text a vbCR with a vbCR & vbLf
I do not really understand why. But googling seemed to indicate it made some sort of sense.

_...............................................
I did some testing of this phenomena here:
http://www.eileenslounge.com/viewtopic. ... 06#p176106" onclick="window.open(this.href);return false;
_..................................................

I am posting the codes again rather than editing the previous ones, as I am not really sure of why/ and whether it should always be done. So in the two different versions you have still the choice

BTW this is the important bit:-
= Replace((Selection.Text), vbCr, vbCr & vbLf, 1, -1) 'In Selection.Text~~,~~~replce a vbCr~~~,~~~with a vbCr & vbLf~~~~,~~~~the returned string should start at position 1 of the original ( so whole string returned )(Note: the number is not just where you start replacing- it is also where the returned String may start-so a number greater than 1 will "chop" bits off returning a string of reduced length compared with the original~~~,~~~-1~~indicates replace all occurrences

_ ……………….

Here are the code versions with the “vbCR & vbLf bodge”

'Using the "Dialogue Find Replace" way
.
Sub AlanHansClipboardTextGetFindReplaceLightBlue

Code: Select all

'
Sub AlanHansClipboardTextGetFindReplaceLightBlue() 'Using the "Dialogue Find Replace" way.   http://www.eileenslounge.com/viewtopic.php?f=26&t=22603
Rem 1) Put Selected Text in Clipboard.
Dim objCliS As dataobject   '**Early Binding.   Object from the class MS Forms, This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. It has the Methods I need to send text to the Clipboard. I will use this to put Things in the Clipboard. Bringing things out I will do with another Data Object
Set objCliS = New dataobject '**Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.
' ( or instead of those two lines Dim obj As New DataObject which is the same ).  or  next two lines are...
'Dim objCliS As Object ' ...Late Binding equivalent'
'Set objCliS = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim Txtin As String: Let Txtin = Selection.Text: Debug.Print Txtin 'Copies the selection as a continuous string: Hit Ctrl G to see it in the Immediate window! You will see it with carriage returns , the Copmuter just sees it as a long "Horizontal" string
objCliS.SetText Txtin 'Make object's text equal above string variable
objCliS.PutInClipboard 'Place current object dataObject into the Clipboard ( Our original selected text ....!!!.... is in that )
'Rem 2) 'Bit of a bodge to get the text in a selection: create a Word file and paste to it
Dim FullFilePathAndFullName As String 'Initial Pigion Hole given for this String variable, and given a special vbNullString "Value", theoretically to simplify comparisons.
Documents.Add: ActiveDocument.Content.Paste 'Make a File Copy in current Application based on Default Type : And Paste from Clipoard ( ...!!!...our original selected text ) using the Default Copy which should at least have all the text, which is all we are interested in here.
ActiveDocument.SaveAs FileName:="TempBBCodeCopyTidledInSpaces.docx", FileFormat:=wdFormatXMLDocument 'Without this the document will not really "exist jet". It has a tempory name ( Used in Windows referrence ), but no path.
Let FullFilePathAndFullName = ActiveDocument.Path & "\" & ActiveDocument.Name
Selection.WholeStory 'Selects whole document which here is just our selection of interest from the oroiginal document
'Rem 3) Han's Text Find Replacement Dialogue 'http://www.eileenslounge.com/viewtopic.php?f=26&t=22603#p175712
    With Selection.Find 'This is the VBA code ( or very similar ) used by Excel when Using the Find eplace text Dialogue box. So this is an improved version of what a macro recording would give.
    .ClearFormatting: .Replacement.ClearFormatting    ' Don't use formating, ? not sure this comes into the equation ??
    .Wrap = wdFindStop    ' Tell Word not to continue past the end of the selection ( And therefore prevents also a display Alert asking )
    .MatchWildcards = False    ' Don't use wildcards. The default anyway, but in this code is an important concept...
    .Text = "  "    ' Search text is two spaces
    .Replacement.Text = "~~"    ' Replace text is with two tildas.
    .Execute Replace:=wdReplaceAll    ' Replace all within selection. This is the "OK" button!
    .Text = "~ " ' Search text is tilda followed by space
    .Execute Replace:=wdReplaceAll    ' Replace all within selection. This is the "OK" button!
    .Text = "~{1;}" 'or  [~]{1;}  It is still not totally clear whether this is a Reg Ex Pattern or a Wild Card String. Important is that it is a String in a Dialogue to be applied to A ( Word in this case ) document. Sort of as you write in a cell, so the ; , convention must be carefully checked and appropriately used here
    '.Text = "~{1,}" ' English XL !!!! *********
    .Replacement.Text = "[color=#BFFFFF]^&[/color]"    ' Enclose in BB codes  ...... This "Wildcard" applies only to the Replace. It inserts the found string, or strings.
    .MatchWildcards = True 'The next line does the Replce, here we are still selecting an option,( Use wildcards )
    .Execute Replace:=wdReplaceAll ' Replace all within selection. This is the "OK" button!
    End With
ActiveDocument.Select 'Re select the...( actually this line alone seems to do it )
Selection.WholeStory '...while document
Rem 4) "Reset the "Find Replace Text Dialogue" "Thing" "
    With Selection.Find
    .ClearFormatting: .Replacement.ClearFormatting: .Text = "": .Replacement.Text = "":  .Forward = True: .Wrap = wdFindAsk: .Format = False: .MatchCase = False: .MatchWholeWord = False: .MatchKashida = False: .MatchDiacritics = False: .MatchAlefHamza = False: .MatchControl = False: .MatchWildcards = False: .MatchSoundsLike = False: .MatchAllWordForms = False '
    End With
Rem 4.5) Bodge due to Forum "Eating a carriage Return sometimes" . Seems to occur when I paste first in Word, then copy that to Forum. Direct to Forum seems OK.
Dim TextAndvbLf As String
Let TextAndvbLf = Replace((Selection.Text), vbCr, vbCr & vbLf, 1, -1) 'In Selection.Text  ,   replce a vbCr   ,   with a vbCr & vbLf    ,    the returned string should start at position 1 of the original ( so whole string returned )(Note: the number is not just where you start replacing- it is also where the returned String may start-so a number greater than 1 will "chop" bits off returning a string of reduced length compared with the original   ,   -1  indicates replace all occurrences
Rem 5) Final result to and from Clipboard
'5b) Using again objCliS we put the modified text in the Clipboard, so overwritng the original
objCliS.SetText TextAndvbLf 'Replace the text in the data object
objCliS.PutInClipboard 'Place current object dataObject into the Clipboard, so putting the modified text in there
'5b) Another data Object to get the data from the clipboard.
Dim objDat As dataobject
Set objDat = New dataobject 'Set to a new Instance ( Blue Print ) of dataobject
'Dim obj As Object
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDat.GetFromClipboard 'All that is in the Clipboard goes in this Data Object second instance of the Class.
Dim TxtOut As String: Let TxtOut = objDat.GetText() 'retrieve the text in this second instance of the Class. ( In this case all I have in it is the text )
MsgBox prompt:="You dumped in Clipboard this " & vbCr & objCliS.GetText() & vbCr & "and if you try to get it, you should get" & vbCr & TxtOut & ""
Rem 6) Optional to delete Temporary File
ActiveDocument.Close (wdDoNotSaveChanges) 'Giving the option will also prevent being asked for it. You must close. VBA will not let you kill an open sheet, as you are affectively working on a copy, and VBA is assumng the Original can be got at by saving for example.  http://www.mrexcel.com/forum/excel-questions/920451-excel-macro-files.html#post4425428
Kill FullFilePathAndFullName 'Use the Kill wisely!!!!  - where this goes there 'aint no coming back!!

Rem 7) End clean up.
Set objCliS = Nothing ' Good practice... maybe....
Set objDat = Nothing ' '  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
End Sub
_.......................................

'Using REGular Expresssions as Direct alternative to a "Dialogue Find Replace" way.


Sub REGinaldsExpressingWTFToReplace

Code: Select all

Sub REGinaldsExpressingWTFToReplace() 'Using  REGular Expresssions as Direct alternative to a "Dialogue Find Replace" way.  http://www.eileenslounge.com/viewtopic.php?f=26&t=22603&start=20
On Error GoTo TheEnd 'Good to do for unpredcable but Likely errors as i don't know what i am doing, so rather than error go and do important stuff before ending
Rem 1) String for Reg Exp from Selection
Dim ORefiginalText As String: Let ORefiginalText = Selection.Text
Rem 2) Replace Tidlies Using simple VBA Strings collection Functions.
Let ORefiginalText = Replace(ORefiginalText, Space(2), "~~", 1, 200) 'Within ORefiginalText, replace 2 spaces,  with  two tidlie wonks, considering ( and outputing ) from positon 1 - so whole string      ,     put a "safty limit" of 200
Let ORefiginalText = Replace(ORefiginalText, "~ ", "~~", 1, -1) '-1 is the special Long number reserved for replace all
Rem 3) Set up our Reg Ex stuff.
Dim regEx As RegExp 'Early Binding so you will need..
Set regEx = New RegExp '....a reference to the "Microsoft vbscript regular expressions 5.5"
'Dim regEx As Object: Set regEx = CreateObject("vbscript.regexp") 'These two lines late Binding alternative
With regEx
    .Global = True 'ignoring cases while regex engine performs the search??
    .MultiLine = True
    .IgnoreCase = False 'restricting regex to find only first match.
End With
Rem 4) Do the Pattern matching ( "Fill up" the Reg Ex object withh all relavant info for a succesful Pattern match
    regEx.Pattern = "~{1,}" ' or  [~]{1,}   Set up the Pattern ( String type ) to be looked for. For Argument types see http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops   or   ask jindon but he will probably not tell me!
        If regEx.test(ORefiginalText) = True Then '...try regEx.test() Method which attempts to find and hold all matches....If it works ( at least one "Pattern" match found ) then....
        Dim MtchIdx As Long 'Loop Bound variable Count, to go through all matches
        Dim RegAndAlldIdxBits() As String: ReDim RegAndAlldIdxBits(1 To 3, 1 To 1) 'Will hold idx Bits of the found string , the start, and regies long length in a convenient string format as Strings are well behaved usually when they look like numbers  and are taken into typical VBA things expecting Numbers. It must be dynamiic to allow a ReDim as / if more matches are found.
                'Dim vTemp: Let vTemp = regEx.Replace(ORefiginalText, "[color=#BFFFFF]^&[/color]"): Debug.Print vTemp ' Dont work !!
        Rem 5 ) loop through the sucessfull Pattern match items held within the Reg Ex object ( A "list2 is mad deatiling each sucessful match
        '                       Dim Cnt As Long: Let Cnt = regEx.Execute.Count '   Don't work !!!!
            For MtchIdx = 1 To 1000 Step 1 'to consider all the Matches regEx.test() Method Found, which regEx gives an identifieng Number starting at 0. 1000 is chosen arbritrarily, mostly we will stop a long time before this###
            Dim Concat As String 'String to collect ( concatenate ) all our matches into one single string for Function to return
            On Error GoTo EndOfRegiesBit 'Predictably, hopefully the over next line will error when we overshoot the availyble Items held by regEx Object
            ReDim Preserve RegAndAlldIdxBits(1 To 3, 1 To MtchIdx) 'We may only REDim Preserve the last dimension. The first time around it ReDims to what it is. Otherwise it increases a column to take in Regies Values and All his Bits
            Let RegAndAlldIdxBits(1, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).Value 'If no error then we have another Item, returned by the .Item() Property. I seem to have learned fron a Jindon's code that    -1 as the index starts at 0 as often for these internally created things
            On Error GoTo 0 'It is genarally good practice to "unplug" the Error handler after use. We do not need it further here. At thois point it would not have been "switched on" to error handlers. Consequently no "VBA Exceptional Error situation was raised and so the Error Handler Statement On Error GoTo = is sufficint to "unplug it". ( Once an Error situation was raised an additional On Error GoTo -1 wouzld have been necerssary initially to take VBA out of its exceptional state. This is because in this exceptional state it, amougst other things, assumes the Error is being taken care of, so ignors all other Error Handler Statements, including On Error GoTo 0 )
            Let RegAndAlldIdxBits(2, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).FirstIndex: Let RegAndAlldIdxBits(3, MtchIdx) = regEx.Execute(ORefiginalText).Item(MtchIdx - 1).Length
            Next MtchIdx 'Go back and look again for pattern match
        Else '.. ( No Pattern found not even 1 )....
        End If
Rem 6)  Error Handler Code Section.   http://excelmatters.com/2015/03/17/on-error-wtf/
EndOfRegiesBit: 'We should come here at the point that no more Matches are found, ( but we had at least one ). The next line is the Error handling code section. After that it is normal code
On Error GoTo -1: On Error GoTo TheEnd 'We bring VBA out of its exceptionally by error raised state, then a putting the for unpredictable Error handler back iin overwrites the last one "switched on"
Rem 7) Loop using Regies  all bits Array to put in BB Code tags in Wanted places. Referring changes back to ORefiginalText
Dim posBBStt As Long, posBBStp As Long 'Variables for point in string where
    For MtchIdx = (UBound(RegAndAlldIdxBits(), 2) - 1) To 1 Step -1 'We intend adding things , so we go backwards...http://www.excelforum.com/showthread.php?t=1103077&p=4184673#post4184673    -1  as the last  was doone before the error
    Let posBBStt = (RegAndAlldIdxBits(2, MtchIdx) + 1) 'Reginald's Array has in second "row" the number of characters before the point we want. The position at which we want start putting a BBCode tag in is one to ther right of this
    Let posBBStp = ((posBBStt + RegAndAlldIdxBits(3, MtchIdx)) + 0) 'Prelifery of Params for shg   http://www.mrexcel.com/forum/lounge-v-2-0/911516-mathematics-not-universal-language-2.html#post4383255
    Debug.Print ORefiginalText '  Folowing are examples based on a TestReg string of 123   789  ( or 123~~~789 ) after Rem 2)
    '6a) Inserting Stop BB Code tag. Here a Copy value of ORefiginalText is taken over to the RHS of the = and worked on using String Object Functions
'    = Replace(ORefiginalText, "~", "~[/color]", (posBBStp - 1), 1): Debug.Print ORefiginalText 'Here this would   (   in  ORefiginalText  ,  replace a tidlie  ,  with a Tidlie and the Stop BB Code tag  ,   starting at the tidlie just before where we wanted the BB Code  ,  ( only doing it once ( as would be anyway ) )     ) giving us something like this   ~[/color]789
'     = Left(RegAndAlldIdxBits(1, MtchIdx), (Len(RegAndAlldIdxBits(1, MtchIdx)) - 1)): Debug.Print ORefiginalText 'This would be require for the last line to give us the tidlie string minus 1 tidlie
'      = Left(ORefiginalText, (posBBStt - 1)): Debug.Print ORefiginalText 'This would give the string up to the position just before where we want to insert the start BBCode Tag. This would give something like   123
    'Let ORefiginalText = Left(ORefiginalText, (posBBStt - 1)) & Left(RegAndAlldIdxBits(1, MtchIdx), (Len(RegAndAlldIdxBits(1, MtchIdx)) - 1)) & Replace(ORefiginalText, "~", "~[/color]", (posBBStp - 1), 1)
    'Alternative not requiring regies Value ( Value being the found Tidlie thing. )
'    = Right(ORefiginalText, ((Len(ORefiginalText) - posBBStp) + 1)): Debug.Print ORefiginalText 'This would give the string after the Point where we want to insert the BBCode Stop tag. We need for this the length counting from the right which will be 1 more than the total length ( currently ) and the to be inserted point for the Stop BB Code. This would give something like this  789
'     = Left(ORefiginalText, (posBBStp - 1)) 'This would give the string up to the position just before where we want to insert the stop BBCode Tag. This would give something like   123~~~
    Let ORefiginalText = Left(ORefiginalText, (posBBStp - 1)) & "[/color]" & Right(ORefiginalText, ((Len(ORefiginalText) - posBBStp) + 1)): Debug.Print ORefiginalText
    '6b) Inserting Start BB Code tag. Here a Copy value of last by referring to changed ORefiginalText is taken over to the RHS of the = and manipulated using String Object Functions
    '= Left(ORefiginalText, (posBBStt - 1)): Debug.Print ORefiginalText 'returns like   123
    ' = Right(ORefiginalText, ((Len(ORefiginalText) - posBBStt) + 1)): Debug.Print ORefiginalText ' gives like this  ~~~[/color]789
    Let ORefiginalText = Left(ORefiginalText, (posBBStt - 1)) & "[color=#BFFFFF]" & Right(ORefiginalText, ((Len(ORefiginalText) - posBBStt) + 1)): Debug.Print ORefiginalText
    'Let ORefiginalText = ORefiginalText & vbCr  ' Strangely this "bodge seemed to be needed initially. It was as if a return was always lost when pasting into a Forum. So putting one in where not wanted after each match cured that.- 2  became 1 so has 1 where wanted!!.  but never the less had a lot at the end !?
    Next MtchIdx ' Got to next held item in Reginald

Rem 8)  Clipboard
'8a)(i) Dump in Clipboard This used to put in Clipboard
Dim objCliS As dataobject   '**Early Binding.   This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. So I name it CLIpboardSend. But it is a DataObject. It has the Methods I need to send text to the Clipboard
Set objCliS = New dataobject '**Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.
' ( or instead of those two lines  Dim obj As New DataObject ).    or  next two lines are.....Late Binding equivalent'
'Dim obj As Object'  Late Binding equivalent'   If you declare a variable as Object, you are late binding it.  http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
objCliS.SetText ORefiginalText 'Make Data object's text equal to a copy of ORefiginalText
objCliS.PutInClipboard 'Place current Data object into the Clipboard
Rem 8a)(ii) Bodge for Word Problem:  If I do a "stop over" at Word, that is to say paste first into Word, then manualy copy that back to the Clipboard again then paste that in the Forum Editor, then I seem to loose my Carriage return. TRhis Bodge seems to vercome this:
Dim TextAndvbLf As String
Let TextAndvbLf = Selection.Text: Let TextAndvbLf = Replace(ORefiginalText, vbCr, vbCr & vbLf, 1, -1) ''In ORefiginalText  ,   replce a vbCr   ,   with a vbCr & vbLf    ,    the returned string should start at position 1 of the original ( so whole string returned )(Note: the number is not just where you start replacing- it is also where the returned String may start-so a number greater than 1 will "chop" bits off returning a string of reduced length compared with the original   ,   -1  indicates replace all occurrences
objCliS.SetText TextAndvbLf  'Make Data object's text equal to a copy of TextAndvbLf
objCliS.PutInClipboard 'Place current Data object into the Clipboard
'8b) Get from clipboard. This a Another Object from class to be sure we have the data in the Clipboard
Dim objDat As dataobject
Set objDat = New dataobject 'Set to a new Instance ( Blue Print ) of dataobject
'Dim obj As Object
'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDat.GetFromClipboard 'All that is in the Clipboard goes in this Data Object second instance of the Class.
Dim TxtOut As String: Let TxtOut = objDat.GetText() 'retrieve the text in this second instance of the Class. ( In this case all I have in it is the text )
MsgBox prompt:="You dumped in Clipboard this " & vbCr & objCliS.GetText() & vbCr & "and if you try to get it, you should get" & vbCr & TxtOut & ""
'
Rem 9) End clean up.
TheEnd: ' ( Come here always, even on a unpredictable error )
Set regEx = Nothing '   Good practice...   maybe....
Set objCliS = Nothing '  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
Set objDat = Nothing
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word VBA Replace multiple Spaces in Text with BB Code St

Post by ChrisGreaves »

HansV wrote:
28 Feb 2016, 16:34

Code: Select all

Sub Replace2OrMoreSpaces()
:thankyou:
Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by SpeakEasy »

Here's a slightly shorter, cleaner regexp solution in a macro called Example ...

Code: Select all

 ' Does global search and replace as per specs of the current selection
 ' Word document remains unaltered
 ' Modified text is left on the clipboard
Public Sub Example()
    Dim strsource
    Dim dataobj As Object
    
    strsource = Selection
    Set dataobj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = " (?= )" ' we could do this with a single match if onbl VbScriopt's regexp engine supported lookbehind
        strsource = .Replace(strsource, "~")
        .Pattern = "([~]{2,}) "
        dataobj.SetText .Replace(strsource, "[color=#BFFFFF]$1~[/color]")
    End With
    
    dataobj.PutInClipboard
End Sub

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by ChrisGreaves »

SpeakEasy wrote:
19 Oct 2021, 12:30
Here's a slightly shorter, cleaner regexp solution in a macro called Example ...
OK
I'll bite:

Why "New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}" rather than the name of the whatever-it-is we are making a NEW of?
Thanks
Chris
Last edited by ChrisGreaves on 21 Oct 2021, 10:55, edited 1 time in total.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by HansV »

It is a way to create a MSForms DataObject using late binding.
A line such as

Code: Select all

    Set dataobj = CreateObject("MSForms.DataObject")
does not work.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by Doc.AElstein »

Hello Chris,
I was often puzzled about that strange syntax. Maybe you can do that as an alternative way to Late Bind for all things if you can figure out what the number in { } should be??? . I don’t know
I think Rory claims to have found that one, but he’s not saying where or how…
https://web.archive.org/web/20140610055 ... ataobject/
( https://web.archive.org/web/20140610024 ... y-listbox/ )

I suppose if you had a lot of time , and/or a spare computer you could set it off on some brut force macro** , one with error handling in it, to try out all number/ letter combinations in the { } and for every number that it does not error, try to find out what you get maybe with something like typename(obj) ?
( ** those sort of brut force macros are to find on the internet, but I don’t think we are supposed to talk about them – House Rule 12. )



Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by HansV »

Try searching RegEdit for DataObject.
Best wishes,
Hans

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by SpeakEasy »

>Why "New:{1

It is just a (woefully under documented) alternative to the better known class name method, using the CLSID instead. This is necessary here, as there is no useable class name for late binding a DataObject from scratch.

And if you want to early bind, then you have to require that there is at least 1 userform in the Forms collection (not a form you have to actually use, though). This causes a reference to MSForms to be added to the project, and then the following works fine:

Code: Select all

    Dim dataobj As MSForms.DataObject
    Set dataobj = New MSForms.DataObject

And yes, the CLSID method works fine with other CLSIDs, eg:

Code: Select all

    Dim objIE As Object
    Set objIE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
    MsgBox TypeName(objIE)
But clearly use of class names is the better option, as otherwise we are resorting to obscure magic numbers. And not all CLSIDs represent COM objects that can be directly instantiated by VBA

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by Doc.AElstein »

HansV wrote:
21 Oct 2021, 10:48
Try searching RegEdit for DataObject.
Hi Hans,
I could not find it. Searching for DataObject in RegEdit seems to find a lot of stuff, a few times. I don’t see how to find that number. But maybe I looked wrong, I did like:
Image

Possibly searching the RegEdit is a subject in itself
( I think the search seems to have a memory, - after it found it a few times it never found it again , not even after re starting the RegEdit - I had to restart the computer to get it to find it again )


I tried the other way around and looked for 1C3B4210-F441-11CE-B9EA-00AA006B1A69. I could not find that anywhere using the search box

I was only curious, it’s not important. It may be something you can never find unless you know where it is, so searching for it is no good

I had a manual look around randomly a few years back and found 1C3B4210-F441-11CE-B9EA-00AA006B1A69 in the registry, but I couldn’t find it today

Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by HansV »

RegEdit opens at the location where you last closed it. To start a search from the top, click at the top. In fact, it is sufficient to select HKEY_LOCAL_MACHINE. Everything else is actually a subkey of that.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by Doc.AElstein »

I thought it might be something like that – but did not know where I should be clicking on
If I do that, it finds DataObject 3 times if I search again and again from Strg+F after the first search, but no sign of that number anywhere

I am sure i did see that number in the registry a few years ago, but I am bugged if i can find it today. But it's not important, it was just curiosity
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by HansV »

This is the first hit when I search RegEdit for DataObject:

S0068.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by Doc.AElstein »

I get this, and nothing is highlighted in the left side window
ImageImageImage

After it finds those first 3 it tells me it's finished searching the registry. But I only tried on one computer.

I can find what you find, as long as I already know where it is.
Image

I think my memory is coming back, and it was in the CLSID where I looked some time ago…Yes, my memory is coming back to me now….
I found on the internet some API macro stuff to list out those CLSIDs
Unfortunately they only listed a few of them, only a small number of them, and nothing I was looking for
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Word VBA Replace multiple Spaces in Text with BB Code String

Post by ChrisGreaves »

Hans > It is a way to create a MSForms DataObject using late binding.
A line such as
Set dataobj = CreateObject("MSForms.DataObject")
does not work.

Does not work, or does not work very well? I get the general idea about early late binding. To me it is similar to the difference between a fully-compiled object deck for a mainframe, and a semi-compiled that must be finalized with a link-loader to hook up the library code before the thing can actually execute; and there are advantages for both methods.
I am struggling to understand why it might be necessary to use an awkward string of alphanumerics instead of a recognizable string of “words”. Same argument for URLs and IP addresses, Variable names and absolute RAM locations.
I am struggling to come to terms with NOT being able to use a descriptive name such as "MSForms.DataObject".
I am quite happy with anyone who wants to save microseconds by using something fixed and absolute rather than variable and relative. Well, almost happy ...

SpeakEasy >
CG>Why "New:"
It is just a (woefully under documented) alternative to the better known class name method, using the CLSID instead. This is necessary here, as there is no useable class name for late binding a DataObject from scratch.
This answers in part my doubts expressed above. “It is an alternative”.
But I am way out of my depth with “there is no useable class name for late binding a DataObject from scratch”. It is not that there is no name for the Data Object (like “MSForms.DataObject”), just that it can’t be done?
As Hans said, too.
Puzzling; all my VBA life I have heard about early/late binding as if either way is achievable; now it seems there are limitations.
I confess, shamefully, that when first I saw your "New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}", I thought that you were showing-off, the way I do when I talk about the ICL mainframe’s “Memory Word 19” rather than “The program Counter”
Now I think that you are saying that the only way to get this done is to know of a 36-character string in the registry. And presumably, you get one of those characters wrong and you are dead-in-the-water.

SpeakEasy > And if you want to early bind, then you have to require that there is at least 1 userform in the Forms collection ... But clearly use of class names is the better option, as otherwise we are resorting to obscure magic numbers.
I shall sleep peacefully tonight, hearing you say “ ... otherwise we are resorting to obscure magic numbers”.
My remaining puzzle is why I have never needed to employ 36-character strings before; perhaps because I use early binding by default without knowing it.

As you both might guess, I am easily thrown by Microsoft’s new terminology, whereby they use different names for concepts that seemed to be well-established and well-named years ago.

Thank you both for the elucidation :evilgrin:
Cheers
Chris
An expensive day out: Wallet and Grimace