Import Excel file to array to fill ComboBox

[Pentoast]

I've been having a bit of trouble getting this to work. I have arrived
at the point where the combo box gets filled with the information, but
I'm not familiar enough with VBScript to figure out the rest. Here is
what I have so far:

Sub Item_Open()

Set FormPage = Item.GetInspector.ModifiedFormPages("More Info")
Set Control = FormPage.Controls("cboCategories")

On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
Err.Clear
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set Mybook = objXL.Workbooks.Add("C:\test\test.xls")
Mybook.Worksheets("Sheet1").Activate

MyVariable=objXL.Columns("a").Value

Set Mybook=Nothing
Set objXL=Nothing

Control.List() = MyVariable

End Sub


As you can tell, it loads all the information from Column A. The
problem is that it loads the ENTIRE column, not just the cells that are
filled. What can I do to filter out the empty cells?

 

[Sue Mosher [MVP-Outlook]]

What about defining a range that covers all the cells you want to import?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Pentoast]

That would probably work better, but as I said before, I'm pretty new
at this, so I'm not too sure how to define the range that I need. I
tried this, but it didn't work.

MyVariable=objXL.Cells(1, 1).Cells(1, 10).Value

I'm just not sure what exactly to use. Any code or help will be
greatly appreciated. Thanks!

 

[Sue Mosher [MVP-Outlook]]

From Excel Help, this is the correct way to define a range with start and end points:

Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3))

I was actually thinking of using an existing named range defined manually, though.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Pentoast]

how would I do what you were thinking?

 

[Sue Mosher [MVP-Outlook]]

Insert | Name | Define. You can then refer to the range by name. Details in Excel Help.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Pentoast]

Ok, I figured that out and it works up to the point before the combobox
is populated by the information.

I get the error "Could not set the List property. Invalid property
Array Index." when it hits the line "Control.list() = MyArray"

I'm not sure how to properly index it.

 

[Sue Mosher [MVP-Outlook]]

Sounds like MyArray isn't an array, even though that's what you named it. Can you show the code snippet you use to get MyArray?

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Pentoast]

oh... I'm sorry, I must have renamed it somewhere along the way just so
I could identify it better. It's actually in the code in the first
post. It is named "myVariable" in that code. Thanks!

 

[Sue Mosher [MVP-Outlook]]

It would be really helpful if you'd include the relevant information from previous messages. Unless you do, those of us using offline readers have to go back and retrieve the earlier part of the thread, which will delay any response.

Your statement

MyVariable=objXL.Columns("a").Value

does not return an array. This one does:

MyVariable=objXL.Columns("a")

However, it's an array generated from all 65536 cells in the column, probably not what you're looking for. You might want to use a named range or specify a range of specific cells you want to use.

Also, there are newsgroups specifically for Excel issues if you need more assistance on using things like Columns and Range.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers