creating a dropdown dynamically in multiple rows & assiging values to it from database

Worksheet.DropDowns.Add(Worksheet.Range(Erow, Erow).Left, Worksheet.Range(Erow, Erow).Top - 1, Worksheet.Range(Erow, Erow).Width, Worksheet.Range(Erow, Erow).Height).caption = ""

i have teh above code from vb.net to create dropdown in excel dynamically

this is working fine
the dropdowns are displayed in each row as per the range given
i am openinng excel from Vb.net

the issue is each dropdown has differnt name
i need to populate all the dropdowns created with data from datbase in vba

as the dropdowns r created dynamically how do i get the names to populate the dropdowns in vba

i jst have the above code for creating dropdown for the range of rows in a partcialr column
how do i create a reference in vb.net & access it in vba workbook?

I am opening the Excel sheet Template from vb.net with few records from vb.net ie Exporting data from the application
when the Excel template gets opened with the data the user needs to just eneter Qty in each row & select a data from the dropdown which is created in each line

so i think i dont need the reference of dropdown in vb.net once its exported,
if i am able to populated the dropdown in vba as all the dropdown will have the same data from datatable

let me know whts the best


populating the dropdown from vb.net code & then exporting wuld take time , i suppose

i wuld be exporting the items , ie itenmane , itmid, qty, item desciption ,, etc

the items can be 50- 100 at a time so it will take say 100 rows of excel sheet & there will be 100 dropdowns
so is there a way by which i can populate the dropwn when its exported but before opened for user

as said by you i tried to populate the dropdown created from code

below code creates the dropdown for n no of rows

Worksheet.DropDowns.Add(Worksheet.Range(ExcRow, ExcRow).Left, Worksheet.Range(ExcRow, ExcRow).Top - 1, Worksheet.Range(ExcRow, ExcRow).Width, Worksheet.Range(ExcRow, ExcRow).Height).caption = ""

but i get tge error as

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Web.UI.WebControls.DropDownList'. Instances of types that represent COM components cannot be cast
to types that do not represent COM

i feel it little confusing

i am creating the dropdown for excel dynamically from vb.net code , so the names of the controls are not knwn
instead of populating it from vb.net code how do i populate each dropdown controls created with data from database
2) i exported some data to excel with blank dropdown
when i try to save the exported excle sheet i get error saying that the excel is corrpouted & cannot be saved

if i remove the code for dropdown i can save it properly

my need as explained is

1) data from database will be exported to excel with X no of items
when exporting column K needs to have dropdown filled with data from database
the dropdown shld appear in all the X rows
so i had created the dropdown using .ADD method from my vb.net code

2) after exporting the user will select values from the dropdown & save the excel sheet for importing in database

hope i am clear
how do i create a reference to dropdown or how fo i get the id or name of each dropdown created so that it can be used in code