Microsoft Office Forums - View Single Post - How can I turn contents of a variable into a table in word?

notapantsday · 11-13-2022, 09:00 AM

I have an Excel worksheet and I'm trying to make a macro that will open a word document and generate a text based on the information in the worksheet.

What my macro does so far: write the information from the worksheet into variables, open a word document, insert the content of the variables.

Now I want to use the contents of one specific variable and do some extra tasks in Word. The variable (string) has a list of items that are separated by either a comma or a semicolon (followed by a space). Here's what I want the macro to do:

1. replace every semicolon with a comma to make it consistent
2. replace every comma+space with a new paragraph
3. convert it into a table with one column and every paragraph as a new line
4. make the border invisible
5. itemize the list

So what I did is I let my normal Macro run and insert the contents of the variables. Then I marked the list that I wanted to work with, started the macro recorder and did the five steps above by hand. This is the code produced by the macro recorder:

Code:

Sub ND()
'
' ND Makro
'
'
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ";"
        .Replacement.Text = ","
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = ", "
        .Replacement.Text = "^p"
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.ConvertToTable Separator:=wdSeparateByParagraphs, NumColumns:=1, _
         NumRows:=7, AutoFitBehavior:=wdAutoFitFixed
    With Selection.Tables(1)
        .Style = "Tabellenraster"
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
    End With
    Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
End Sub

I just can't wrap my head around how I can integrate this code into my macro. Because this is just working on text that was previously selected by the user, but I want it to use the contents of that one variable.

Can anyone help me out here? I have some experience with VBA in Excel, but virtually none in Word.