How can I turn contents of a variable into a table in word?

notapantsday · #1 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.

gmayor · #2 11-13-2022, 10:04 PM

I assume that you want to run this macro from Excel. The string here, to be replaced by your variable is sList. The macro opens a Word document and creates a one column table that contains the list, which I think is what you are trying to achieve. You can integrate this with your existing menu.

Code:

Sub Macro1()
Dim wdApp As Object
Dim oDoc As Object
Dim oTable As Object
Dim sList As String
Dim vList As Variant
Dim oRng As Object
Dim i As Integer
Dim iRow As Object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    sList = "This is; a test, of a string" 'the variable
    sList = Replace(sList, ";", ",")
    Set oDoc = wdApp.Documents.Add
    Set oRng = oDoc.Range
    oRng.Collapse 0
    Set oTable = oDoc.Tables.Add(oRng, 1, 1)
    vList = Split(sList, ",")
    oTable.Cell(1, 1).Range.Text = Trim(vList(0))
    For i = 1 To UBound(vList)
        Set iRow = oTable.Rows.Add
        oTable.Cell(iRow.Index, 1).Range.Text = Trim(CStr(vList(i)))
    Next i
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word Table of Contents - when I title a heading "table", table of contents shows "table2"	jncuk	Word	8	04-15-2020 07:01 PM
Mail Merge with variable table length MS Word 2013	rbr	Mail Merge	6	11-18-2016 07:05 AM
Turn Word table cell into clickable hyperlink button	cobms2014	Word	3	03-26-2015 09:10 AM
Using macro to add variable number of rows to a protected word table	Julia	Word Tables	1	01-09-2013 06:04 AM
Display contents of global variable in headers	KenBrown	Word	2	08-16-2012 07:47 AM