CODEWITHSUNDEEP
vbasplitms-word
Ahmed Enzea
Ahmed Enzea

Reputation: 55

VBA Split data by new line word

I am trying to split data using VBA within word.

I have got the data using the following method

d = ActiveDocument.Tables(1).Cell(1, 1).Range.Text

This works and gets the correct data. Data for this example is

This
is
a
test

However, when I need to split the string into a list of strings using the delimiter as \n

Here is an example of the desired output

This,is,a,test

I am currently using

Dim dataTesting() As String
dataTesting() = Split(d, vbLf)
Debug.Print dataTesting(0)

However, this returns all the data and not just the first line.

Here is what I have tried within the Split function

Upvotes: 1

Views: 4384

Answers (3)

ViktorasCNC
ViktorasCNC

Reputation: 11

Dim dataTesting() As String
dataTesting() = Split(d, vbLf)
Debug.Print dataTesting(0)

works fine and thank you very much for your example, for why it have returned a whole array is because you have used 0 as index, in many programming languages 0 is the whole array, so the first element is , so in my case counting from 1 this perfectly split a string that I had troubles with.

To be more exact this is how it was used in my case

Dim dataTesting() As String
    dataTesting() = Split(Document.LatheMachineSetup.Heads.Item(1).Comment, vbCrLf)
    MsgBox (dataTesting(1))

And that comment is a multiline string.

Image

So this msg box returned exactly first line.

Upvotes: 0

Cindy Meister
Cindy Meister

Reputation: 25693

Word uses vbCr (ANSI 13) to write a "new" paragraph (created when you press ENTER) - represented in the Word UI by ¶ if the display of non-printing characters is activated.

In this case, the table cell content you show would look like this

This¶
is¶
a¶
test¶

The correct way to split an array delimited by a pilcro in Word is:

Dim d as String
d = ActiveDocument.Tables(1).Cell(1, 1).Range.Text
Dim dataTesting() As String
dataTesting() = Split(d, vbCr)
Debug.Print dataTesting(0)  'result is "This"

Upvotes: 1

Roemer
Roemer

Reputation: 1271

You can try this (regex splitter from this thread)

Sub fff()
    Dim d As String
    Dim dataTesting() As String

    d = ActiveDocument.Tables(1).Cell(1, 1).Range.Text
    dataTesting() = SplitRe(d, "\s+")

    Debug.Print "1:" & dataTesting(0)
    Debug.Print "2:" & dataTesting(1)
    Debug.Print "3:" & dataTesting(2)
    Debug.Print "4:" & dataTesting(3)
End Sub

Public Function SplitRe(Text As String, Pattern As String, Optional IgnoreCase As Boolean) As String()
    Static re As Object

    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Global = True
        re.MultiLine = True
    End If

    re.IgnoreCase = IgnoreCase
    re.Pattern = Pattern
    SplitRe = Strings.Split(re.Replace(Text, ChrW(-1)), ChrW(-1))
End Function

If this doesn't work, there may be strange unicode/Wprd characters in your Word doc. It may be soft breaks, for instance. You could try to not split with "\W+" in stead of "\s+". I cannot test this without your document.

Upvotes: 0

Related Questions