Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    vlookup and combobox (excel 2000)

    Hi,
    does an expert know the solution to my problem ?

    I have a workbook with on a sheet a collection of data
    ref lot description etc;
    500 2658 item a
    501 5871 item b
    502 2361 item c
    503 4872 item d

    in a DIFFERENT WORKBOOK there is a userform
    when i fill in the lot n

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    If I understand what you are after, Assume that (change as appropriate)
    the Lot# is in cell A1 of the open workbook.
    The closed workbook is named WorkbookClosed.xls
    The workbook is in the folder "C:Folder"
    The datatable is in a sheet called "DataSheet"
    The data range is B1:G100 (A has the "ref column" as indicated by your data, B has the ref no, C the description, etc)

    To get the description (in Column C) you can use the VLOOKUP with a "link" to the closed file:

    =VLOOKUP(A1,'C:Folder[WorkbookClosed.xls]DataSheet'!$B$1:$G$100,2,FALSE)

    The next column (Column D) you would use:
    =VLOOKUP(A1,'C:Folder[WorkbookClosed.xls]DataSheet'!$B$1:$G$100,3,FALSE)

    Etc

    If this doesn't answer your question, could you elaborate?
    Steve

  3. #3
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: vlookup and combobox (excel 2000)

    Steve,
    this looks ok but has an errorcode when applied = compile error / syntaxerror
    and the cursor stops at the first ' of the line
    remember this all is in te code of a userform

    Private Sub CBOK_Click()
    Dim pw$, datum$: pw = "boost"
    Sheets("Aankomstbericht").Activate
    ActiveSheet.Unprotect (pw)
    datum = Range("b6").Value
    Range("b8").Value = TBREF.Value
    Range("e8").Value = TBLOT.Value
    Dim lot: lot = Val(TBLOT.Value)
    tbref.caption = VLOOKUP(lot,'n:noel[lotnummers.xls]lotnummers'!$b$1:$G$100,1,FALSE) ' error in this line
    Range("b18").Value = TBREF.caption
    Range("b18").Value = TBLICHTER.Value


    any suggestions ?
    jan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    This is a formula for a cell not a VB. I think if you need to do a Link in VB, you will have to put the formula into a cell and then read that cell value into VB

    Private Sub CBOK_Click()
    Dim pw$, datum$: pw = "boost"
    Sheets("Aankomstbericht").Activate
    ActiveSheet.Unprotect (pw)
    datum = Range("b6").Value
    Range("b8").Value = TBREF.Value
    Range("e8").Value = TBLOT.Value
    <font color=red>'Dim lot: lot = Val(TBLOT.Value)
    Range("F8").Formula = "=VLOOKUP(E8,'n:noel[lotnummers.xls]lotnummers'!$b$1:$G$100,1,FALSE)"
    'tbref.caption = VLOOKUP(lot,'n:noel[lotnummers.xls]lotnummers'!$b$1:$G$100,1,FALSE) ' error in this line
    tbref.caption = range("F8").value 'if needed
    Range("b18").Value = Range("f8").value ' or TBREF.caption</font color=red>
    Range("b18").Value = TBLICHTER.Value



    Steve

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: vlookup and combobox (excel 2000)

    Steve,

    is there a way to do this in vba, too?

    Thanks for the advice.
    jan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    I do not know. Perhaps someone else will post a response.

    There is either a very simple way, (that I am unaware of)

    OR

    it is much more complex and cumbersome than simply using the formula approach using a cell in Excel that I outlined to you.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and combobox (excel 2000)

    Do you have a particular reason for not wanting to use formulas?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    Hans,
    Are you aware of a way with VB to "link" to a file?

    The only way I know to get info from a "closed" file using VB is to open the file, read the info, and then close the file. This (to me at least <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) just isn't the same as "linking" to a file.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and combobox (excel 2000)

    Steve,

    No, that's the only way I can think of too. Some formulas can handle closed workbooks, but as far as I know, VBA cannot do anything with the cells in a closed workbook; it needs to open it first. As you indicated in <post#=362650>post 362650</post#> higher up in this thread, Jan could enter the VLOOKUP formula in a cell, then get the result of the formula in VBA. I was wondering why he doesn't want to use this approach.

    (Using DSOFile.dll, it is possible to read/write some document properties in a closed workbook, but that doesn't help here.)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    Thanks. Its good to know I'm not missing something simple.

    Steve

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: vlookup and combobox (excel 2000)

    I don't know about "linking" an Excel file, but as alternative to opening file you can open an ADO recordset to read the data. Simple example:

    Public Sub TestODBCConnection()
    On Error GoTo Err_Handler

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim lngCount As Long
    Dim n As Long
    Dim i As Integer
    Dim strMsg As String

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    ' Use generic OLE DB Provider for ODBC Drivers with User DSN:
    cnn.ConnectionString = "Provider=MSDASQL;DSN=TestData;"
    cnn.Open
    strSQL = "SELECT * FROM RANGE1;" ' Named range
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
    lngCount = rst.RecordCount
    rst.MoveFirst

    ' Test results (if small number of records) - print column headings:
    For i = 0 To rst.Fields.Count - 1
    Debug.Print "Field " & (i + 1) & ": " & rst.Fields(i).Name
    Next i
    Debug.Print ' blank line

    ' Print cell values:
    For n = 1 To lngCount
    For i = 0 To rst.Fields.Count - 1
    ' Offset for Column headings and Fields index (zero-based), assumes range starts at A1:
    ' If more than 26 columns need more complicated formula for Column letters:
    Debug.Print "Cell " & Chr$(i + 65) & (n + 1) & ": " & rst.Fields(i).Value
    Next i
    rst.MoveNext
    Next n

    MsgBox rst.RecordCount, vbInformation, "RECORD COUNT"
    rst.Close
    cnn.Close

    Exit_Sub:
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "TEST ODBC CONNECTION ERROR MSG"
    Resume Exit_Sub
    End Sub

    This uses the generic OLE DB Provider for ODBC Drivers, which requires an ODBC data source (DSN) to be defined & specified in connection string (DSN is named "TestData" in example) (you can create the connection string w/o specifying a DSN, but my attempts to do so successfully with an Excel data source proved futile...) When creating DSN specify MS Excel Driver and name of Excel file (full path). Also note that the SQL statement specifies a named range (RANGE1 in example) to select records; recommend use of named ranges if using this technique (not sure how else you'd specify table name in an Excel data source). In tests, this sub successfully printed out the field names (ie, column headings) and named range's cell data by rows & columns (assumes range starts at A1). Tested OK whether or not file was open (code executed from another spreadsheet). Note: To test this, must set reference to ADO in VB Editor, listed in References dialog as "Microsoft ActiveX Data Objects 2.x Library"; should be available if using XL 2000 or later.

    HTH

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup and combobox (excel 2000)

    I would classify this as part of what I described as:
    <hr>much more complex and cumbersome than simply using the formula approach using a cell in Excel <hr>
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  13. #13
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: vlookup and combobox (excel 2000)

    Steve, Hans,

    it seems i stirred some things. The main reason why i want to avoid formulas is that ,when i tend to put it into a userform (vba) the user cannot wipe by accident the code. Maybe i was mislead by ADO or a combination of vlookup and ADO.

    If anyone sees another way please speak out.
    Jan

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and combobox (excel 2000)

    You could put the formula in a hidden sheet (even "very hidden" from the Visual Basic Editor) and retrieve and use the result of the formula in VBA.
    Or you could put the formula in a protected worksheet, where only the cells to be filled in by the user are unlocked.

  15. #15
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: vlookup and combobox (excel 2000)

    hi,

    the very hidden seems a good idea. There is always someone who likes to break passwords without knowing what he can or cannot change in the code.

    thanks for the idea,
    Jan

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •