Solved: Listbox In Word Pulled From Excel Range

[Ken1000]

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/FillListBoxFromXLDAO.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


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

[lucas]

Hi Ken,
Hope you don't mind, I enclosed your code in vba tags(select code and hit vba button)
Try this:

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


you'll have to set columncount and boundcolumn in the listbox properties box.

[Ken1000]

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

[lucas]

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.

Private Sub ListBox1_Click()
Selection.TypeText ListBox1
Unload UserForm1
End Sub


ps we're all self taught ken. I learned what little I know here.

[Ken1000]

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

[lucas]

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.