VBA ComboBox

[Rodrigues]

All
Firstly,Credits to Debaser for the help on this matter.
I'm coming back to this now as I was "wrapped" with other unexpected stuff.
Attached are two files, one with userform & vba code and other as a master data.
I'm trying to get comobox1 pickup the data from MasterData.xlsx (name manager).
Any help will be appreciated.
Thanks in advance.
R

 

[Chihiro]

In standard module add following UDF.

Based on code found in link.
https://sites.google.com/site/beyon...rksheetsnamedrangesinclosedexcelworkbookswado

Function GetSchema(sPath As String, sWorkbook As String) As String
'  ?GetSchema(Thisworkbook.Path, thisworkbook.Name)
    Dim cn  As Object
    Dim rs  As Object
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
 
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sPath & "\" & sWorkbook & ";" & _
            "Extended Properties=Excel 8.0;"
    Set rs = cn.OpenSchema(20)  '20 =adSchemaTables
 
    While Not rs.EOF
        If Not rs("TABLE_NAME") Like "*$" Then
            GetSchema = GetSchema & _
                IIf(Trim(GetSchema) = "", "", ",") & rs("TABLE_NAME")
        End If
        rs.movenext
    Wend
 
    rs.Close
    cn.Close
 
End Function

Then add following in UserForm module.

Private Sub UserForm_Initialize()
Dim listName As String
Dim x As Variant

listName = GetSchema(ThisWorkbook.Path, "MasterData.xlsx")
x = Split(listName, ",")
Me.ComboBox1.List = x

End Sub

NOTE: Haven't tested with workbook that has tables along with named range. It may populate both.

 

[Rodrigues]

Hi Chihiro
I'm just getting Products on the combobx and not the drop down list. Probably I'm missing something.
Could you please assist.
Thanks for your help.
R

 

[Chihiro]

Ah, I misunderstood you. I thought you needed list of Named Ranges to pick and choose from so that you can specify which named ranges to pull data from.

Forget about my code then.

Modify your code:

1. "Set rst" line change to

Set rst = CreateObject("ADODB.Recordset")

2. Call GetNamedRangeData in your UserForm_Initialize sub.

 

[Rodrigues]

Thanks a lot, just have one more question, just shows cells with numbers, alphanumeric cells are not showing, could you be kind enough and help once again?
Thanks R

 

[Chihiro]

By default, ADODB imports column as value type most frequently occurring in the column (numeric in this case, text will return null). To import alpha & numeric mixed column, add following string at end of your connection string. "IMEX=1;".

So your .ConnectionString becomes...

      .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\MasterData.xlsx;" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"""

Edit:
In general, when importing form Excel file, it's safer to always import with IMEX=1. Then manipulate data type later once imported. Especially when [ColumnName] isn't known and you can't set varchar or other conversion method for each column.

 

[Rodrigues]

Thanks again, that's works perfect, much appreciated.
Regards
R

 

[Rodrigues]

Hi Chihiro
Keeping the same principle, I'm trying to develop this form adding others ComboBoxes, and found that they don't show. Added str query but got run time errors. Could you help please? Thanks
R

 

[Chihiro]

What's the purpose? Should each named range be in it's own ComboBox?

Note that unless you union or join queries, you won't be able to pull multiple Select into same recordset.

Just to let you know, I probably won't be able to look at it till sometime next week as I'm busy this weekend with events.

 

[Rodrigues]

The plan is that, have two separated files on shared drive, one as a master data and other with "userform" which will be used on multiple machines, the reason for that is, "products" on the master data file needs regular updates, so I don't have to update multiple times the same info.
Thanks for your help.
R

 

[Chihiro]

Sorry for late reply I had missed your reply.

Converted GetNamedRangeData to function.

Function GetNamedRangeData(nRange As String) As Variant
' Sample demonstrating how to return a recordset from a named range in a workbook
' NOTE: this will not work with a dynamic named range

  Dim cn As Object, strQuery As String, rst As Object
  Set cn = CreateObject("ADODB.Connection")
  With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=C:\MasterData\MasterData.xlsx;" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"""
      .CursorLocation = 3
      .Open
  End With
  strQuery = "SELECT * FROM " & nRange & ";"
        Debug.Print strQuery
  Set rst = CreateObject("ADODB.Recordset")
  rst.Open strQuery, cn, 1, 3
  GetNamedRangeData = rst.Getrows
 
  rst.Close
  cn.Close
End Function


Private Sub UserForm_Initialize()
Me.ComboBox1.Column = GetNamedRangeData("[Shift]")
Me.ComboBox2.Column = GetNamedRangeData("[Operator]")
Me.ComboBox4.Column = GetNamedRangeData("[JobN]")
Me.ComboBox5.Column = GetNamedRangeData("[Products]")
Me.ComboBox6.Column = GetNamedRangeData("[RMat]")
Me.ComboBox7.Column = GetNamedRangeData("[Supplier]")
End Sub

 

[Rodrigues]

Chihiro, exacatly what I was looking for,works fine.
Regards
R