Array from Word vs array from excel

[SpookyAwol]

BACKGROUND
Word / VB template system. Userform to define what documents are generated and custom content. Generates up to 20 unique documents.

Currently data (for drop downs) is stored in a word document in tabular format which is then put into an array then multiselect box. A user can select one or multiple records.

I maintain a controlled source for this word document in an excel spreadsheet. I cut and paste any updates from the excel (as a whole new table) into my existing word document.

QUESTION
Can I save myself a step and increase code efficiency/speed by just querying the excel spreadsheet directly?
If worthwhile, tips on code?

CURRENT CODE

Set Sourcedoc = Documents.Open(FileName:=sFileName + "source.doc") i = Sourcedoc.Tables(1).Rows.Count - 1
ListBox1.ColumnCount = 5
ListBox4.ColumnCount = 5

Dim MyArray() As Variant
ReDim MyArray(i, 5)

For n = 0 To 4 ' 0 to 4 columns (i place holder)
For m = 0 To i - 1 ' 0 to i lines
If n = 4 Then
MyArray(m, n) = m
Else
Set myitem = Sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Set myitem = Nothing
End If
Next m
Next n
Sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
Set Sourcedoc = Nothing

[Frosty]

Well, the Excel forum will probably be more useful to getting the actual code you'll use to retrieve info from the excel spreadsheet.

You'll eventually need to use GetObject and/or CreateObject to get the Excel Application (or launch one) in order to open the spreadsheet (would recommend read-only, so you can have multiple people access, if it ever happens).

That said, what I would do is post this question in the Excel forum (since I imagine you're pretty unfamiliar with that object model), and then build your array entirely within Excel VBA.

Once you've got your function which fills your MyArray variable in Excel, the porting of the code to Word will offer you two choices:
1. Late-Binding (this is where you'll basically be working blind-- and change many of your excel object variables (like Worksheet, WorkBook, Application) to object data types.
2. Early-Binding - add a reference in your Word template to the Excel Object library, and you'll be able to access those items directly-- however, make note that when you port your excel code over, you'll need to specify objects which have the same name as an explicit type. So Dim myApp As Excel.Application ... otherwise Word VBA would think you want to work with a Word.Application.

But yes, I'd say save yourself a step, and query Excel directly. It's worth the effort, and you'll learn a good bit about cross-application programming.

That said -- I don't think you'll get a performance increase in the final product... you'll have added launching an entirely separate application to the process-- with the requisite still needing to open a file to get data. You will decrease the "user-error" type issues that can come when you're copying/pasting an excel table into Word, and any massaging you do. You *might* be able to use an excel spreadsheet as a datasource (as opposed to opening it in an application, and then simply cycling through rows/columns to get data) for some kind of SQL query, but I've never done that... so it may not be possible.
And sometimes, simpler is better.

My suggestion: why don't you try and build the array in Excel VBA (using the more Excel VBA gurus in that forum -- I always have to wade my way through the Excel object model, because I'm not as intimately familiar with it), and then you'll get a sense of how much is involved, and whether you want to learn about the late-binding/early-binding and getobject/createobject parts of cross-app programming.

[westconn1]

personally i find it so much simpler working with excel than word

[gmaxey]

You can find some tips and code for populating Word userform listboxes from Excel or Access here:

http://gregmaxey.mvps.org/word_tip_pages/populate_userform_listbox_or_combobox.html

[SpookyAwol]

Cheers for that, Ill have a play