Consulting

Results 1 to 6 of 6

Thread: Solved: Listbox In Word Pulled From Excel Range

  1. #1
    VBAX Regular
    Joined
    Aug 2005
    Posts
    17
    Location

    Solved: Listbox In Word Pulled From Excel Range

    Hi. I am using Office 2003 and need help with some VBA interaction
    between Word and Excel.

    I came upon some code entitled "Load a ListBox from a Named Range
    in Excel using DAO" from this site:

    http://word.mvps.org/FAQs/InterDev/F...xFromXLDAO.htm

    I wanted to use this code to test the possiblility of pulling from a
    one column range with 500 items in an Excel file.

    (I thought this code would fill up the listbox with these 500 names.)

    I made sure the Excel file from which the range was to be pulled is in
    the same directory as the Word document file which is calling it. Also,
    I was sure to add the property reference for Microsoft DAO 3.6. I then
    created a userform and set up the code below to initialize when the
    userform was loaded via a macro.

    However, when I run the macro that loads the userform, the code
    below does not work. Instead, this error pops up : Run time error
    3170: could not find installable ISAM.

    (In fact, the userform does not even load ... just the message above
    appears.)

    Could you review my code below and tell me what I'm doing wrong,
    please? Perhaps there's some typo I have missed, or some brain fog
    I'm under. Or, could it possibly be that the intended range from Excel
    is too big? (500 items)

    Or is there a better/simpler way to do this using ADO instead of DAO?
    (Pull Excel Range row data into a listbox on a Userform in Word)

    If so, could you please share with me the code to accomplish this.

    Thank you very much for your help.

    Ken

    [VBA]
    Private Sub UserForm_Initialize()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
    Dim xfile As String

    'get path for Excel file from this same directory
    xfile = ThisDocument.Path & "\" & "CustomerDBTest1.xls"

    Set db = OpenDatabase(xfile, False, False, "Excel 11.0")

    'note: I tried the code line below and it yields the same error
    'Set db = OpenDatabase(xfile, False, False, "Excel 8.0")

    ' Retrieve the recordset, in this case, it is called CustomerDBInfo1;
    ' must include in apostrophes the range name from the Excel file
    Set rs = db.OpenRecordset("SELECT * FROM `CustomerDBInfo1`")

    ' Determine the number of retrieved records
    With rs
    .MoveLast
    NoOfRecords = .RecordCount
    .MoveFirst
    End With

    ' Set the number of Columns = number of Fields in recordset
    ListBox1.ColumnCount = rs.Fields.Count

    ' Load the ListBox with the retrieved records
    ListBox1.Column = rs.GetRows(NoOfRecords)

    ' Cleanup
    rs.Close
    db.Close

    Set rs = Nothing
    Set db = Nothing

    End Sub
    [/VBA]
    Last edited by Ken1000; 04-13-2006 at 03:52 PM.

  2. #2
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi Ken,
    Hope you don't mind, I enclosed your code in vba tags(select code and hit vba button)
    Try this:
    [vba]
    Private Sub UserForm_Initialize()
    Dim objExcel As New Excel.Application
    Dim wb As Excel.Workbook
    Dim FName As String
    Dim Tmp

    FName = "f:\AAA\Data1.xls"
    Set wb = objExcel.Workbooks.Open(FName)

    ListBox1.List = wb.Sheets(1).Range("AllData").Value
    Canceled:
    objExcel.Quit
    End Sub
    [/vba]

    you'll have to set columncount and boundcolumn in the listbox properties box.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular
    Joined
    Aug 2005
    Posts
    17
    Location
    Thank you, Steve, that works great.

    One more question, and please forgive my ignorance, as I am self-taught
    in VBA ...

    How would I get the user's selection (the ListBox1.Value) into memory so
    that after the userform was unloaded, this same text could be pasted on
    the Word doc?

    Thank you very, very much.

    Ken

  4. #4
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    No need to put it in memory......just click the line you want entered....be sure the cursor is where you want the info to be inserted.
    [vba]
    Private Sub ListBox1_Click()
    Selection.TypeText ListBox1
    Unload UserForm1
    End Sub
    [/vba]

    ps we're all self taught ken. I learned what little I know here.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    VBAX Regular
    Joined
    Aug 2005
    Posts
    17
    Location
    Steve, thank you for the super fast reply and the excellent code.
    I guess we're all still learning. I appreciate this.

    How do I mark this thread as "solved"?

    Ken

  6. #6
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Glad to help. Heck Ken, I've got a 21 year old son thats making me relearn my calc, chemistry and physics......this is not so hard...

    Forum is undergoing an upgrade right now and its not working. I will mark it solved for you.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •