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??
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"
Set oWbk = oExcel.Workbooks.Open("name of excel file.xls"
'this saves and closes excel
oWbk.Save
oWbk.Close True
oExcel.Quit
to read from a cell in excel:
oWbk.Sheets(2).Range("A1".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.
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"
Set oWbk = oExcel.Workbooks.Open("name of excel file.xls"
'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 = ""
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,
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.