vba mylookup combobox and label caption

[Rodrigues]

All
Credits to Chihiro, as he has helped with this matter when first query it.
The problem now is that, was trying to adapt the same code to a different approach, which is looking to a combobox & label.caption from an different worksheet (files attached).
In a nutshell what I would like to achieve is that:

When an item on ComboBox 5 is selected, lookup to label50.caption and on MasterData.xlsx source file & FProducts sheet and display the respective number on TextBox8.
I.e.:
Label50.caption=1
ComboBox5=AB100
TextBox8 should display 10
Thanks in advance for any help.
Regards
R

 

[Chihiro]

You'd need another function like GetNamedRangeData.

Sample with Column and criteria hard coded. Try replacing hard coded portion with your variable.

Note: Use HDR=Yes, as you need header for select statement.

Function GetData() As Variant

  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:\Test\Combine\MasterData.xlsx;" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;"""
      .CursorLocation = 3
      .Open
  End With
  strQuery = "SELECT [1] FROM [lstStat] Where [Products]='AB100';"
  Set rst = CreateObject("ADODB.Recordset")
  rst.Open strQuery, cn, 1, 3
  GetData = rst.Getrows
  rst.Close
  cn.Close
End Function

Sub test()


Debug.Print GetData(0, 0)

End Sub

EDIT: FYI - I wasn't able to import from Table so converted table into named range "lstStat" and used that for query.

 

[Rodrigues]

Hi Chihiro
Reverted table1 and replaced by named range "lstStat", added Funtion to a Module, point it to my path, however TextBox8 won't show anything, also struggled to set [Products] to variable. I must be doing something wrong, could you please assist?
Thanks
R

 

[Chihiro]

Don't use Table, but use entire sheet.

I couldn't find documentation, but when I test, ADODB isn't able to find [Table1] object.

Since you have only 1 table in the sheet, use [FProducts$] to pull info (i.e. sheet name followed by $).

Demo of how to use variable in strQuery.

Function GetData(cString As String) As Variant

  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:\Test\MasterData.xlsx;" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;"""
      .CursorLocation = 3
      .Open
  End With
    strQuery = "SELECT [1] FROM [FProducts$] Where [Products]='" & cString & "';"
      Set rst = CreateObject("ADODB.Recordset")
  rst.Open strQuery, cn, 1, 3
  GetData = rst.Getrows
  rst.Close
  cn.Close
End Function

Sub test()

myArray = GetData("AB100")

Debug.Print myArray(0, 0)

End Sub

 

[Rodrigues]

I'm really sorry, cannot make it work. TextBox8 still not showing results from CBO5 selected items.

 

[Chihiro]

Did you forget to change path to your own? I used C:\Test\... and I see in your file that you didn't change this.



Also GetData function argument should not be sheet name, but your criteria for [Products] (i.e. your ComboBox5 value).

I moved Functions into standard module from UserForm1.

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 & ";"
  Set rst = CreateObject("ADODB.Recordset")
  rst.Open strQuery, cn, 1, 3
  GetNamedRangeData = rst.Getrows
  rst.Close
  cn.Close
End Function
Function GetData(sCol As String, cString As String) As Variant
'sCol is Column Header/Label50.Caption, cString is Product/Combobox5.Value
  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=Yes;IMEX=1;"""
      .CursorLocation = 3
      .Open
  End With
    strQuery = "SELECT [" & sCol & "] FROM [FProducts$] Where [Products]='" & cString & "';"
      Set rst = CreateObject("ADODB.Recordset")
  rst.Open strQuery, cn, 1, 3
  GetData = rst.Getrows
  rst.Close
  cn.Close
End Function

In userform...

Private Sub ComboBox5_Change()
With Me
    myResult = GetData(.Label50.Caption, .ComboBox5.Value)
    .TextBox8.Value = myResult(0, 0)
End With

End Sub

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

See attached.

 

[Rodrigues]

Chihiro
Thank you so much, this works fine.
Regards
Americo