Word Listbox from Excel Worksheet (Office XP)

Topic

New Reply

The string on Combo boxes is very, very similar to my own issue, but I’ve read so much today:

I have a table in WordPerfect format (don’t ask — I’m trying to “leverage” here). The table holds info about users. If you open it in Word it has some extra line breaks within cells, but the data is intact. I think I can loop through the cells and clean the data, but…

By the way, it is a simple table with no header (ergo no field names).

Anyway, I’d like to populate a listbox with the info in the table so it can be used in the creation of a document.

I *could* save the WP document as an Excel 4 spreadsheet, though I’d like to just access the WP file, if at all possible. But if I do have to save to another format, I can’t seem to populate a listbox from an Excel spreadsheet, either.

Does anyone have any suggestions? Thanks in advance!
Kim

Reply | Quote

Replies

I don’t understand why you need a list box to create a document, but OK.

Word can open WordPerfect documents, but it can’t use a table in a WP document as source for a list box. This holds for other Office apps too.

If you convert the WP document to an Excel workbook, it depends on the structure of the table whether you can use it as row source for a list box, but you should be able to use a “straight” table for that purpose.

Reply | Quote

Hi Hans,

I want to present the user with a list of names (with other bits of info). They can choose one or more and insert information into the document. The template in which this listbox resides also gathers a lot of other information to ultimately compile the document. The WP table also presents this same office with names for their WP templates — it’s used in a lot of ways. This is why I would like to keep the document in WP. It gives the client one place to change info and one file to distribute. This is why I’m trying to make it work.

I have pulled the WP table document into Word and run various macros on it to clean it up. No reason I couldn’t then save it.

But the bottom line is that I have had no luck populating a listbox data from anywhere. I had code from the MVP site (populating a listbox from a named range in Excel) that gives me an error message that it can’t find the object I’m trying to use. There’s clearly something I’m missing — a reference, perhaps — that is keeping me from successfully getting the data into the listbox.

I hope I’ve explained it a little better. Thanks for your response.
Kim

Reply | Quote

The code from the MVP site requires that
– You have set a reference to the Microsoft DAO 3.6 Object Library (in Tools | References…)
– You have imported the list from WP into an Excel workbook, and you have given the list a name (select the entire list, and type the name in the cell address box, or select Insert | Name | Define…)
– You have specified the same name in the SQL statement, enclosed in ‘backwards quotes”: `

Reply | Quote

Hi again!

[Edited to add further question]

I had the reference right. I had the quotes wrong. *Backwards* quotes! Made all the difference.

[Further Question:] I am having some odd problems displaying the data now. Remember that I said I had no header row. If I don’t include a blank row at the top of my Excel spreadsheet, and then include the blank row in my named range, I don’t capture the first record.

If I define a range of the first column only and all 12 rows, I see every record in my listbox. Hurray!
If I define a range of the 6 columns and 12 rows, I display only the first record in my listbox. Hmmm. This is better than before, when I got nothing (then I added the blank row at the top of the range…) There are only so many things to tweak so I’m at a bit of a standstill.

Any ideas of what I may be missing?

Thanks Hans!!
Kim

Reply | Quote

The extra row is necessary because a recordset in DAO (or ADO always needs field names; these are retrieved from the first row. If the first row of the named range contains data, those data won’t be displayed in the list box. Try filling the first (now empty) row with dummy field names (F1 through F6 will do).

If that doesn’t work, set a breakpoint at the start of the Initialize procedure. Single step through it using F8 and inspect the value of variables by hovering the mouse above them. Does NoOfRecords get set correctly? This variable in the code from the MVPS site is used to set the number of records to be retrieved in

ListBox1.Column = rs.GetRows(NoOfRecords)

Reply | Quote

Hi Hans,

I filled the blank row with field names and stepped through the code again. The NoOfRecords variable is getting set accurately, but only the first row of data is being displayed. I even redefined my named range, but I still only return the first row.

If I use the identical code, but SELECT * FROM a named range consisting only of the first column, I do get all rows returned, but am I correct that I have not loaded all the info in the other columns (and outside the named range) into memory?

Thanks again,
Kim

Reply | Quote

Strange, I can get a list box to display multiple rows and columns. Can you post the workbook you have used to test? (Replace sensitive data with dummy data if necessary)

Reply | Quote

[Edited to include extra info]

Here is the file. And here is its history:

Originally a WordPerfect table.
Used Save As in WP to save as Excel 4.0 spreadsheet.
Opened in Excel XP and saved as current version spreadsheet.
There are two named ranges that I’ve been testing – _author which is just the first column and _authors which is all columns

The code from MVP refers to “Excel 8.0” but if I change to later version, I’m told I don’t have an appropriate ISAM.

[Extra info:] The problem seems to lie in the file. I created a new Excel file and the code worked on it. On the other hand, I am able to display all rows if the named range only consists of one column (the _author range). Aarrrrgghhhh.

So there we are…

Thanks so much,
Kim

Reply | Quote

The problem is in the ‘Bar’ field. The first record (Monkee) contains a number in the Bar field, so DAO assumes it is a numeric field. The second record (movie star) contains the text value NA in the Bar field. Apparently this causes DAO to quit retrieving records without raising an error.

If you clear the NA values (replace them by nothing), all records will be displayed in the list box.

This is the type of thing we would never find out without seeing the data

Reply | Quote

Yippee!!

And being an Excel novice, even seeing the data did me no good at all…

I am much better educated having gone through this. Thank you, thank you, thank you, etc.

Kim

Reply | Quote