Sunday, August 25, 2019

MS word drop down control with Excel data

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.




Watch the step by step tutorial in my YouTube channel 







3 comments:

  1. This is for those one who cannot handle any task in Excel. I do not think that it is very difficult, just a little experience needed.

    ReplyDelete
  2. Excel is so well developed software that it can simply solve a lot of tasks without any other programs and economy the space on your Hard Drive.

    ReplyDelete
  3. Hello thank you for this tutorial but it doesn't work for me at this line Me.drop.Clear
    what should i do please?

    ReplyDelete

How to Fix ImportError: No module named 'flask' with virtualenv

Flask is one of the most used frameworks in Python. If you are new to the flask and if you are getting this error - ImportError: No module n...