Import ComboBox value from Excel into MS ACCESS 2003 database via vba

How did you get the drop-down values into the combo boxes?  They look like form controls, but I would normally expect to see the input source set when you select Format control from the right click menu.  I can't find any useful properties for them when I read them as ControlSource objects in VBA

I have requested that the Excel Zone be added to this Q...

Here is the VB that I used to create the drop down boxes:

        With .Range("C12:C12")
            Set xlComb = xlWS.DropDowns.Add(.Left, .Top, .Width, .Height)
        End With
        With xlComb
           .AddItem "Full-Time"
           .AddItem "Part-Time"
        End With

Sorry for the delay, family sickness and travel has kept me away.

This code reads the value from the dropdown and places it in the cell to the right.  Some cells have more than one dropdown and some are not properly initialised, hence the error handling.  If there is more than value in the dropdown, I have copied the value into the first empty cell to the right

The Dropdown object you have used is now a hidden object, presumably because MS want you to use ActiveX objects instead - I had never come across this one.  I'll post some code to add a more standard dropdown in a few minutes.

Sub ExtractDropdownText()

    Dim d As Excel.DropDown
    Dim vVal As Variant
    Dim rngOut As Excel.Range
    
    For Each d In ActiveSheet.DropDowns
        vVal = Empty
        On Error Resume Next
        vVal = d.List(d.ListIndex)
        On Error GoTo 0
        
        If Not IsEmpty(vVal) Then
            ' place the value in the first empty cell to the right of the dropdown
            ' (some cells have more than one dropdown)
            Set rngOut = d.TopLeftCell.Offset(0, 1)
            Do While Not (IsEmpty(rngOut.Value))
                Set rngOut = rngOut.Offset(0, 1)
            Loop
            rngOut.Value = vVal
        End If
    Next d
    
    
End Sub

Select allOpen in new window

Hi,

I recieved an error with this line of the code:

cbx As MSForms.ComboBox

With this error: "User-defined type not defined"

Any thoughts on how to resolve?

andrewssd3,

What References in the Object Library do I need to have active for this function to work.  I think that I might not have the reference required.

Yes you need to add a reference to Microsoft Forms 2.0 Object Library - I already had that.  You do it through Tools...References from the VBA editor, then find and check it in the list

Microsoft Forms 2.0 Object Library is not avaialble in the Library.  How do you add it?

You should have it.  If you add a userform to your project it should automatically add the reference - then you can remove the userform and the reference stays..

Great News!  It works one time.  I imported the information and it showed up in my database like a charm.

The problem is that I will need to make a few hundred spreadsheets from a database.  If I run one excel spreadsheet and close the application and run one spreadsheet again, it works fine.  If I try to run two spreadsheets or run it more than once, there is a Global error.  

I think that something in the Public Sub code is not referenced correctly, such as Set to 'Nothing'.  I don't really know how to determine where the information needs to be referenced, and I spent about 4 hours trying, but this is where someone else has helped in the past.

I've attached a sample db that creates the sample spreadsheet from one table to mimic what I am trying to do.   I have asked it to make two excel (from the same data) spreadsheets to mimic the error I am getting.

Any suggestions would be greatly appreciated.  Sorry that I am not more up on which one of these variables is remaining open and causing the error.
test-wdropdown.mdb

andrewssd3,

I will try this today and let you know if it works.  Thanks for your help.

This worked.  I created about 40 files and all have imported with the data from the drop down.  Thanks much.