topic Re: populating vba combobox with spreadsheet range name in Inventor Programming Forum

populating vba combobox with spreadsheet range name

MCCJohnPetty — Mon, 04 Nov 2019 21:28:24 GMT

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"

Re: populating vba combobox with spreadsheet range name

asmenut — Tue, 05 Nov 2019 16:25:16 GMT

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

Re: populating vba combobox with spreadsheet range name

jR0sal3s — Thu, 05 Mar 2020 07:03:48 GMT

how do you specify the spreadsheet file?

Re: populating vba combobox with spreadsheet range name

asmenut — Thu, 05 Mar 2020 12:01:00 GMT

If you have the excel sheet linked then something like this

'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")

Re: populating vba combobox with spreadsheet range name

jR0sal3s — Fri, 06 Mar 2020 04:51:36 GMT

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.

Re: populating vba combobox with spreadsheet range name

asmenut — Fri, 06 Mar 2020 12:05:16 GMT

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

Re: populating vba combobox with spreadsheet range name

jR0sal3s — Fri, 06 Mar 2020 22:43:06 GMT

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