Ken1000
04-13-2006, 03:37 PM
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
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