KrishTalk.com: August 2019

There are lots of things you can do with MS Excel and MS word. Word and Excel can be connected easily to build sophisticated use cases. Mail merge is one of the great tool, where you can leverage the data power of excel and reporting feature of the MS Word. With a bit of VBA knowledge, you can extend the mail merge for usages.

Below I explain how to use the Excel data to populate the DropDown control in the MS word. And it is dynamic, when the data in Excel got changed, the items in the drop-down will also be changed.

First, I have this sample data in Excel.

I want this data to be displayed in a dropdown control in the MS Word. For this I am making use of this macro or VBA code.

Private Sub ld_Click()

Dim objExcel As New Excel.Application

Dim objWB As Excel.Workbook

Set objWB = objExcel.Workbooks.Open("C:\Users\krsanjee\Documents\krishtalk.com\book1.xlsx")

'Me.drop.RemoveItem (0)

Me.drop.Clear

total_rows = Cells(Rows.Count, 1).End(xlUp).Row

total_columns = Cells(1, Columns.Count).End(xlToLeft).Column

With Me.drop

For r = 2 To total_rows Step 1

.AddItem objWB.Sheets(1).Cells(r, 1)

Next r

End With

objWB.Close

End Sub

Note the Sub ld_Click() Subroutine, it has been assigned to a button control. Clicking on this button you can load the dropdown control with the data from Excel

Compare the value in the first column in the Excel and the values in the MS Word dropdown. I am using the first column that is a serial number because I needed the Sl No. to manipulate the data to extract other columns from the Excel for reporting purpose.

The code has been changed slightly to get the EMP Name in the dropdown.

With Me.drop

For r = 2 To total_rows Step 1

.AddItem objWB.Sheets(1).Cells(r, 2)

Next r

End With

In the .AddItem objWB.Sheets(1).Cells(r, 2) line I have changed the column number to 2 so that the EMP Name will be populated in the dropdown.