Solved

Using vbscript to read from an excel file and populate a drop down list

Posted on 2008-10-09
2
1,658 Views
Last Modified: 2008-10-12
I have an excel file that contains a list of names in one row and the distinguished name in another.


I need to read the file and output the names into the drop down box while having the distinguished name be the value for the selection.

The following was my attempt, but was unsuccessful.

strXLpath = "c:\managers.xls"
      
            Set objXL = CreateObject("Excel.Application")
            objXL.visible = False
            Set objWb = objXL.WorkBooks.Open(strXLpath)
            

            objWb.MoveFirst
            Do Until objWb.EOF
                  Set objOption = Document.createElement("OPTION")
                        objOption.Text = objXL.Rows(1).Value
                        objOption.Value = objXL.Rows(2).Value
                        mgrName.Add(objOption)
                        objWB.MoveNext
            Loop


0
Comment
Question by:dhalliday
  • 2
2 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 22682399
Hi, assuming you've got Manager name in column A and their distriguished name in Column B, try this....

Regards,

Rob.
strXLpath = "c:\managers.xls"
 

Set objXL = CreateObject("Excel.Application")

Const xlUp = -4162

objXL.visible = False

Set objWb = objXL.WorkBooks.Open(strXLpath)

Set objSheet = objWb.Sheets(1)
 

For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row

	Set objOption = Document.createElement("OPTION")

	objOption.Text = objSheet.Cells(intRow, "A").Value

	objOption.Value = objSheet.Cells(intRow, "B").Value

	mgrName.Add(objOption)

Next

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 22699075
Thanks for the grade.

Regards,

Rob.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now