Populate a word form with information from an excel spreadsheet 1

[DrSmyth]

I have a word for which i want to populate with information from an excel spreadsheet, is it possible to accomplish this using VBA and can anybody suggest some coding for achieving this task??

 

[nochoice]

Here is some of my code, I hope it helps you:

Dim oExcel As Object
Dim oWbk As Object

'opening excel in the background
Set oExcel = CreateObject("Excel.application&quot
Set oWbk = oExcel.Workbooks.Open("name of excel file.xls&quot

'this saves and closes excel
oWbk.Save
oWbk.Close True
oExcel.Quit

to read from a cell in excel:

oWbk.Sheets(2).Range("A1&quot.FormulaR1C1 = myvariable

Note: Range can be a variable, Sheets(2) refers to the second sheet in the excel document, it can be Sheets(1), Sheets(3), etc...

This should be all you need to open and read from the excel file, if you need help populating the objects in your form, I can help with that too, let me know.

Cheers,
NoChoice

 

[DrSmyth]

Chers nochoice, this is wicked.... Could you give me a few pointers on populating the objects in my form as well...??

I've currently got word forms on the form itself, would i need to replace them with labels?

 

[nochoice]

Assuming you are populating a listbox or a combobox on a form, first you need to create an array then you fill the box with the array, here is an example of populating MyListBox on MyUserForm:

Dim MyArray(2,100) as String
'If you do not know the dimensions of the array, dimension it like this:
'Dim MyArray() as String

Dim oExcel As Object
Dim oWbk As Object

'opening excel in the background
Set oExcel = CreateObject("Excel.application&quot
Set oWbk = oExcel.Workbooks.Open("name of excel file.xls&quot

'if you do not know the dimensions of your array, you can ReDimension your array like this using variables
'ReDim MyArray (xVar, yVar)

y = 1
'Chr(65) returns the Character associated with the ASCII code 65. 65 is A. I use character codes so I can loop through the columns
rng = Chr(65) & y
'The rng is set to A1

While Not (oWbk.Sheets(1).Range(rng).FormulaR1C1 = "&quot
MyArray(0, (y - 1)) = oWbk.Sheets(1).Range(rng).FormulaR1C1
y = y + 1
rng = Chr(65) & y
Wend

'This populates the array called MyArray from Column A in Sheet 1. It continues to fill the array until it hits an empty cell

'This next line populates MyListBox in MyUserForm
MyUserForm.MyListBox.Column() = MyArray

'this saves and closes excel
oWbk.Save
oWbk.Close True
oExcel.Quit


If you don't know the dimensions of your array, I can help you with that as well,

Cheers,
NoChoice