populating vba combobox with spreadsheet range name

[MCCJohnPetty]

How can I use a spreadsheet range name to populate a combobox  using vba?

i.e. spreadsheet range name = "test range"

combobox name = "cb_testRange"

[asmenut · Accepted solution]

I figured it out (I can't seem to fiqure out the Range name technique but as long as I have a range to work with, it goes like this:

Private Sub UserForm_initialize()

'Define the Inventor application and OLE Desriptors
Dim oApp As Inventor.Application
Set oApp = ThisApplication
Dim oOleRef As ReferencedOLEFileDescriptor
Set oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1)

'Define the WorkBook and WorkSheet(s)
Dim oWB As Excel.Workbook
Call oOleRef.Activate(kEditOpenOLEVerb, oWB)
Dim oSheet1 As WorkSheet
Set oSheet1 = oWB.Worksheets.Item("Belt_Data")

'Populate the Combo Box
cb_BSeries.List = oSheet1.Range("A4:A30").Value

End Sub

[jR0sal3s]

how do you specify the spreadsheet file?

[asmenut]

If you have the excel sheet linked then something like this

'Define the Inventor application and OLE Desriptors
Dim oApp As Inventor.Application
Set oApp = ThisApplication
Dim oOleRef As ReferencedOLEFileDescriptor
Set oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1)

'Define the WorkBook and WorkSheet(s)
Dim oWB As Excel.Workbook
Call oOleRef.Activate(kEditOpenOLEVerb, oWB)
Dim oSheet_BData As WorkSheet
Set oSheet_BData = oWB.Worksheets.Item("Belt_Data")

[jR0sal3s]

how would it know which and where the excel file that contains the data? I don't see any directory here..my point is that, the list box/combo box will show data derived from an excel file.

[asmenut]

Question: Are you linking or embedding your spreadsheet, or are you trying to access your workbook from outside inventor?

If you are linking/embedding your spreadsheet, you don't need to call it explicitly, since it is part of your Inventor file. The attached vba form and code should get you going.

If you are trying to access excel outside of your inventor part, Foxrid3r posted a nice snippet thet explains how to access excel as it's file name:

https://forums.autodesk.com/t5/inventor-customization/excel-vba-to-drive-inventor/td-p/6559926

Hope this helps

[jR0sal3s]

Sweet...this is what i am looking for...Thanks a lot!.