Excel question- searching from Word userform

Factual Questions
1

I have a combobox in a Userform in a Word template that pulls its list of entries from an Excel spreadsheet. There are several columns and I pull all of them into the combobox.

The problem is that there are over 4000 rows in the spreadsheet, with 3 or 4 columns, so it takes the Word userform over 15 minutes to load all of the data.

Is there a better way to do this? I was thinking of maybe having the user enter some data into the userform first, and then write code to search the Excel sheet for that entry and just pull over the relevant rows.

2

In Excel VBA there are ways of reading a range into an array that can take orders of magnitude longer than other ways. How are you loading the data from Excel? Are you using VBA code to read the Excel file, or are you linking the combobox to a range in the Excel file? (I have done a lot of Excel and Word VBA programming but nothing that crosses between the two so I don’t even know if a Word combobox can link to an Excel range.)

3

Actually, I left out that the first thing I am doing is pulling the excel data into a table in a Word document, and then getting the data from there. I’m creating an array of data and then populating the combobox with it. Here is the code:


Dim myarray() As Variant
Dim sourcedoc As Document
Dim i As Integer
Dim j As Integer
Dim myitem As Range
Dim m As Long
Dim n As Long
Application.ScreenUpdating = False
Set sourcedoc = Documents.Open(FileName:="P:\address.doc")
i = sourcedoc.Tables(1).Rows.count - 1
j = sourcedoc.Tables(1).Columns.count
ComboBox1.ColumnCount = j
ReDim myarray(i, j)
For n = 0 To j - 1
    For m = 0 To i - 1
    Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
    myitem.End = myitem.End - 1
    myarray(m, n) = myitem.Text
    Next m
Next n
ComboBox1.List() = myarray
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
4

Just so I understand, do you have any indication of whether the 15 minutes is being spent loading the data from Excel vs. loading your array from the Word table?

I have never loaded an array from a Word table so I don’t know if there’s a faster way to do it. I will have a look at that if I can get some time. (There are very abundant resources for Excel VBA, but not as much for Word VBA. Not sure if this will be found online.)

5

Here is something that looks like it will be valuable. First it notes that

Extracting Data from Word Tables Programmatically
The code is a bit obfuscated for my taste but it looks like it’s worth a try.

ETA: I would change this from a Sub to a Function that returns aDataAll as a Variant.

6

Never mind, I figured it out. I had been trying to do it with a DAO recordset but I kept getting errors. Then I realized I had not added the DAO object library to my references. Once I did that, I was able to pull straight from Excel and it pulls all 4000 rows almost instantly.

Thanks.