Dede Blog: just another Software Development Engineer

In today’s post we see how to create a macro in Visual Basic for Application that allows us to automatically populate a table in a Word document using an external Excel worksheet as data source.

So we create a Word document containing a table with a single line, the one of the column headings, and we save it as a “Word Macro-Enabled Document(.docm)”

We also create an Excel spreadsheet that contains the table with the data we want to export in the Word document. Also in this case we insert the header row, which will contain the same column names of the related Word table.
We also populate the table with some sample lines:

At this point we have to write a VBA macro that will perform the operation.
We go therefore into the Word “View” menu and click the “Macro” button. Selecting in the combo-box the current Word document, we define the macro name we want to create, for example, “PopolaTabellaDaExcel,” and then press the “Create” button, as shown in the following figure:

So we enter the VBA editor, and we are almost ready to write the macro code.
First, however, in order to be able to work with Microsoft Excel objects, we need to add the relevant reference. To do this go into the “Tools” menu and select “References”. Scroll down the list until you find the “Microsoft Excel 15.0 Object Library”, select it and then press “OK”.

At this point the Excel objects library is available and we can define an Excel application or a worksheet.
Let’s try, for example, to insert the following code in the body of our macro and run it.

Public Sub PopolaTabellaDaExcel()
'
' PopolaTabellaDaExcel Macro
'
'
Dim myexl As Excel.Application
Dim myworkbook As Workbooks
Dim myws As Worksheets

Debug.Print "OK"

End Sub

If we run it, we get in the output window the result “OK”, a sign that there were no errors.

If we had not added the reference to the Excel object library, we would get an error because types of the variables we declared would not be recognized. If we try, just for example, to remove the reference we added earlier, and to run again the code of the simple macro written so far we get the following error, also shown in the figure:

Compilation Error: User-defined type not defined

So, we add again the reference to the Excel library and continue.

First we need to open the Excel file containing our data. For simplicity, we define a variable to which we assign the absolute file path. With the Dir VBA function we verify that the file is actually present at the specified path. Otherwise we point out an error. Let’s modify the macro as follows:

Public Sub PopolaTabellaDaExcel()
'
' PopolaTabellaDaExcel Macro
'
'
Dim myexl As Excel.Application
Dim myworkbook As Workbooks
Dim myws As Worksheets
Dim path_file_excel As String

path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx"

If Dir(path_file_excel) = "" Then
    descrizione = "Error: Input Excel file not found " & path_file_excel
    Call MsgBox(descrizione, vbExclamation)
    Exit Sub
End If

Debug.Print "OK"

End Sub

Setting the “path_file_excel” variable to the correct path of the Excel file, we still get as output the string “OK”, a sign that there were no problems.
If we try to put as the value of the variable “path_file_excel” the path of a file that does not exist, we get an error and we are shown the window with the message we have defined, as shown in the following figure:

We proceed and insert the code to open the Excel file and navigate through the sheets. The following lines instantiate an object of type Excel application, open our input file and move on the first sheet to process it:

Set myexl = CreateObject("Excel.Application")
Set myworkbook = myexl.Workbooks.Open(path_file_excel)
Set myws = myexl.Worksheets(1)

We now modify again the macro as follows, adding the code to “close” the objects used, and try to execute it:

Public Sub PopolaTabellaDaExcel()
'
' PopolaTabellaDaExcel Macro
'
'
Dim myexl As Object
Dim myws As Object
Dim myworkbook As Object
Dim path_file_excel As String

path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx"

If Dir(path_file_excel) = "" Then
    descrizione = "Error: Input Excel file not found " & path_file_excel
    Call MsgBox(descrizione, vbExclamation)
    Exit Sub
End If

Set myexl = CreateObject("Excel.Application")
Set myworkbook = myexl.Workbooks.Open(path_file_excel)
Set myws = myexl.Worksheets(1)

Debug.Print "Here we have to put the code that extracts data"

myworkbook.Close
myexl.Quit

Set myws = Nothing
Set myworkbook = Nothing
Set myexl = Nothing

End Sub

Even at this point, we do not get any error.

Now we can finally focus on the logic for data retrieval. What we do is loop through all the table type objects in the Word file and check if the text in the first cell of the table corresponds to what we’re looking for. Once identified the table we want to fill, we start to iterate the cells from the following line, moving to the column on the right until we find an empty cell and going down line by line until we find again an empty cell.
In case in the Excel file there are no data for the table we are looking for, we point out an error.

The code to iterate over all the tables of the active Word document is the following:

Dim tbl As Table
For Each tbl In ActiveDocument.Tables
	. . .
	. . .
Next tbl

To retrieve the value of the first cell in the header row of the Word table we must use the following code:

Dim s As String
s = tbl.Rows(1).Cells(1).Range.Text

We modify again the macro as follows and we try to execute it:

Public Sub PopolaTabellaDaExcel()
'
' PopolaTabellaDaExcel Macro
'
'
Dim myexl As Object
Dim myws As Object
Dim myworkbook As Object
Dim path_file_excel As String

path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx"

If Dir(path_file_excel) = "" Then
    descrizione = "Error: Input Excel file not found " & path_file_excel
    Call MsgBox(descrizione, vbExclamation)
    Exit Sub
End If

Set myexl = CreateObject("Excel.Application")
Set myworkbook = myexl.Workbooks.Open(path_file_excel)
Set myws = myexl.Worksheets(1)

Dim tbl As Table
Dim s As String
For Each tbl In ActiveDocument.Tables
    s = tbl.Rows(1).Cells(1).Range.Text
    Debug.Print "Valore:" & s & "-"
Next tbl

myworkbook.Close
myexl.Quit

Set myws = Nothing
Set myworkbook = Nothing
Set myexl = Nothing

End Sub

With the statement

Debug.Print "Value:" & s & "-"

we print in output the value of the variable “s” that is the first cell of the table header in the Word file. We add a “-” char just to verify what’s the exact value and to check that there are no special chars at the end of the string.
Executing the macro, the result we get is the one shown in the following figure:

As we can see, to separate the end of the string from the “-” char are present both a carriage return and another special character. This is despite in the relative cell they are absolutely not present. We must therefore take this into account when we go to perform the comparison between the Word table, and the search string.

For simplicity, we now define a variable that contains the string with the content of the first cell of the table that we want to fill and that we have to search in the first column of the Excel file. This value, in order to generalize the procedure, should instead be passed as a parameter. Based on the test performed earlier, the strict comparison of equality between the two values fails, because of the special characters that we had identified.

In fact, if we try to edit the macro as follows and execute it:

Public Sub PopolaTabellaDaExcel()
'
' PopolaTabellaDaExcel Macro
'
'
Dim myexl As Object
Dim myws As Object
Dim myworkbook As Object
Dim path_file_excel As String

path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx"

If Dir(path_file_excel) = "" Then
    descrizione = "Error: Input Excel file not found " & path_file_excel
    Call MsgBox(descrizione, vbExclamation)
    Exit Sub
End If

Set myexl = CreateObject("Excel.Application")
Set myworkbook = myexl.Workbooks.Open(path_file_excel)
Set myws = myexl.Worksheets(1)

Dim tbl As Table
Dim s As String
Dim firstCell As String
firstCell = "Codice Articolo"

For Each tbl In ActiveDocument.Tables
    s = tbl.Rows(1).Cells(1).Range.Text

	If s = firstCell Then
        Debug.Print "First table cell found"
    Else
        Debug.Print "First table cell NOT found!"
    End If
Next tbl

myworkbook.Close
myexl.Quit

Set myws = Nothing
Set myworkbook = Nothing
Set myexl = Nothing

End Sub

we get the result shown in the following figure, which indicates that the cell containing the string “Codice Articolo” was not found.

To overcome the problem of special characters, we can use the Instr function and, instead of looking for the exact match between the two values, we check that the text contained in the Word table cell starts with the value we’re looking for.

We then replace the line of code

If s = firstCell Then

with the line

If InStr(1, s, firstCell) Then

If we try to run again the macro, we see that this time the cell containing the value we are looking for has been identified. The following figure acts as a confirmation:

Then we define a boolean variable we use to keep track of the fact that we found or not a table where the value of the first cell is what we’re looking for. Once identified this table, we have to check if in the Excel file are available data with which to populate it. We then use a second Boolean variable to store such information, and to report an error in case of the excel does not contain data for that table.

At this point we have to process the Excel files in order to get the data and we will do it in the second part of the tutorial..