vb.net fill combobox using named ranges (excel)

Question

I'm trying to get my application to read a excel file that works with named ranges.

My excel has alot of named ranges, and I would like to access the values in these named ranges to fill my combobox on my windows-user-form.

But i have no idea how get these values.

For now i have this to access my excel spreadsheet + range

Dim excel As Application = New Application
Dim w As Workbook = excel.Workbooks.Open("C:\temp\test.xlsm")
Dim rng As Range
rng = w.Worksheets("Sheet1").Range("range")
w.Close()

On this "Sheet1" there is a Named Range called "Range" and it contains 6 cells. I want to add these cell values to my combobox1 using the Named Range.

But i can't figure out a way to do this.. Can someone help me? :)

Andrew Mortimer Andrew Mortimer · Accepted Answer · 2015-08-10T13:14:14

You can try using Goto and Selection. Here is a sample that returns an array of values, as per your requirement:

        Dim xl As Excel.Application
        Dim wb As Excel.Workbook

        xl = New Excel.Application
        wb = xl.Workbooks.Open("C:\Temp\range.xlsm")
        xl.Goto("NewNamedRange")
        Dim rng As Excel.Range = xl.Selection
        Debug.Print(rng.Value.ToString)

vb.net fill combobox using named ranges (excel)

1 Answers