Results 1 to 9 of 9

Thread: conecting a function with a database

  1. #1

    conecting a function with a database



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello!

    First sorry for my english I am from switzerland.
    I have a huge problem(for me) that i cannot solve and if you can help me I'll be forever grateful.

    The think Is, I wanna make a function that have 2 konstant values but the thing is this values depends on the kind of chosen product. I have solved with select case but the thing is, that is necessary to change sometimes and I would like to have these constant values in a external excel.

    But for this I don't know how to get the values(from external closed excel) into the variables inside the function. Have to be a function in order to have the quality of handling.

    I have tried with many things but the biggest problem Is that I have to be able to make a lookup inside this database and take the value from oder column

    If you need more dates just ask me about

    thanks for your help

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,745
    Articles
    77
    Blog Entries
    14
    Welcome! What kind of database are you pulling from exactly?

    Sent from my LG-E900h using Board Express
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hello!

    The database will be other excel of 1 sheet that willl have a table with the dates name, a(value to take), b(value to take).

    I was thinking in use VLookup but I dont know, if will be posible because y should activate the workbook & sheet in the other excel, and I don't know how to take the value found for the Function....Or if is posible... jeje

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,745
    Articles
    77
    Blog Entries
    14
    Can you post a set of basic example workbooks? Take out anythind confidential and we'll see if we can come up with an elegant solution.
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hello! I was testing 2 diferet ways one I got but the problem is that only runs with the makro not with the Function (taht is the goal) and the oder It's with ADO that I don't really know but I think it goes to the write file but don't take the information.

    I post the 2 tries and an example of the database that is inside a excel file in oder hard drive (c:\ for example)
    EXAMPLE that runs with makro (easy way) there is a mixture with german and spansh sorry

    Quote Originally Posted by Ken Puls View Post
    Can you post a set of basic example workbooks? Take out anythind confidential and we'll see if we can come up with an elegant solution.

    Code:
    Function PerdidaPresion(ByVal Dichte As Single, ByVal Geschwindigkeit As Single, ByVal Viskositat As Single, ByVal Parametro As String) As Double
      Dim RutaActual, NombreFichero, fic As String
      Dim FilaInicial, FilaFinal, fil As Integer
      Dim a, b, w, d As Single
      Dim solucion, Beta As Double
     
     
     
      'calculo el nombre de la base de datos ubicada en la misma carpeta
      RutaActual = "C:\Dokumente und Einstellungen\glopez\Desktop\DATOS"
      NombreFichero = "KATALOG2.xls"
      fic = RutaActual & "\" & NombreFichero
     
      'intento abrir la BD. si no puedo, lanzo una excepcion
      On Error GoTo GestionErrores
     
     
     
      'abro la BD
      Workbooks.Open fic
     
      a = -1
      b = -1
     
      FilaInicial = 1
      FilaFinal = 256
      'busco el parametro en la col1 y extraigo a y b de las cols 2 y 3
      For fil = FilaInicial To FilaFinal
        If Cells(fil, 1).Value = Parametro Then
          a = Cells(fil, 5).Value
          b = Cells(fil, 6).Value
          Beta = Cells(fil, 7).Value
          d = Cells(fil, 4).Value
     
     
          Exit For
        End If
      Next
     
      ActiveWorkbook.Close
     
     
      'si no se encontraron a y b. aplico la formula alternativa
      If (a = 0) And (b = 0) Then
     
     
        MsgBox Beta
        MsgBox d
     
                            solucion = (((1 - Beta) / (Beta ^ 2)) * (0.72 + ((49 * Beta * Viskositat) / (Dichte * Geschwindigkeit * d * 10 ^ (-6))))) * Dichte * ((Geschwindigkeit ^ 2) / 2) / 10 ^ (5)
     
        PerdidaPresion = solucion
     
     
        Exit Function
      End If
     
      MsgBox a
      MsgBox b
     
     
     
      'si encuentro a y b aplico la formula principal
      solucion = ((((a + ((b * Viskositat) / (Dichte * Geschwindigkeit * 10 ^ (-6)))) * Dichte * ((Geschwindigkeit ^ 2) / 2))) / 10 ^ (5))
      PerdidaPresion = solucion
     
     
      Exit Function
     
     
     
    GestionErrores:
      MsgBox "No se pudo abrir " & NombreFichero, , "Error " & Err.Number
     
     
     
     
      Exit Function
    End Function
    Code:
    Sub probarFormula()
      Dim res As Double
     
      res = PerdidaPresion(800, 2, 0.01, "122/90")
      Cells(2, 3).Value = res
     
    End Sub
    THE SECOND TRY I would like to do in this way or another that you know

    Code:
    Function PerdidaPresion(ByVal Dichte As Single, ByVal Geschwindigkeit As Single, ByVal Viskositat As Single, ByVal Name As String) As Double
      Dim RutaActual, NombreFichero, fic As String
      Dim FilaInicial, FilaFinal, fil As Integer
      Dim a, b, w, d As Single
      Dim solucion, Beta As Double
     
      'Geschwindigkeit=velocity Viskositat= viscosity dichte= density
     
     
     
    'dimensions
    Dim datConnection As ADODB.Connection
    Dim recSet As ADODB.Recordset
    Dim recCampo As ADODB.Field
    Dim strDB, strSQL As String
    Dim i As Long
     
    'going to the file in oder folder
     strDB = "C:\Dokumente und Einstellungen\glopez\Desktop\DATOS\KATALOG2.xls"
     
    'conecting
    Set datConnection = New ADODB.Connection
    Set recSet = New ADODB.Recordset
     
    datConnection.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & strDB
     
     
    'consulta SQL
    'strSQL = "SELECT * FROM [NuestroRango]"
     strSQL = "SELECT * FROM [$A1:Q1000]"
     
    'opening the recordset(I don't know if is correct
    recSet.Open strSQL, datConnection, adOpenStatic
     
     
    'Copy dates
      a = -1
      b = -1
      FilaInicial = 1
      FilaFinal = 200
     
      'For fil = FilaInicial To FilaFinal
     
       ' If Cells(fil, 1).Value = Name Then
        '  a = Cells(fil, 4).Value
         ' b = Cells(fil, 5).Value
          'Beta = Cells(fil, 6).Value
          'd = Cells(fil, 3).Value
     
          'Exit For
        'End If
      ' Next
     
        For fil = FilaInicial To FilaFinal
     
       If Cells(fil, 1).Value = Name Then
          a = Cells(fil, 4).Value
          b = Cells(fil, 5).Value
          Beta = Cells(fil, 6).Value
          d = Cells(fil, 3).Value
     
       Exit For
        End If
     
       Next
     
     
    'closing
    recSet.Close
    datConnection.Close
     
    'close objects
     
    Set recSet = Nothing
    Set datConnection = Nothing
     
     
    'If a & b is 0 I take the oder constant and operate
     
      If (a = 0) And (b = 0) Then
     
     
        MsgBox Beta
        MsgBox d
     
                            solucion = (((1 - Beta) / (Beta ^ 2)) * (0.72 + ((49 * Beta * Viskositat) / (Dichte * Geschwindigkeit * d * 10 ^ (-6))))) * Dichte * ((Geschwindigkeit ^ 2) / 2) / 10 ^ (5)
     
        PerdidaPresion = solucion
     
     
     
        Exit Function
      End If
     
      MsgBox a
      MsgBox b
     
     
     
      'If I find a & b I make the operation
     
      solucion = ((((a + ((b * Viskositat) / (Dichte * Geschwindigkeit * 10 ^ (-6)))) * Dichte * ((Geschwindigkeit ^ 2) / 2))) / 10 ^ (5))
      PerdidaPresion = solucion
     
     
      Exit Function
     
     
     
     
    GestionErrores:
      MsgBox "No se pudo abrir " & NombreFichero, , "Error " & Err.Number
     
     
     
     
      Exit Function
    End Function
    Code:
    Sub probarFormula()
      Dim res As Double
     
      res = PerdidaPresion(800, 2, 0.01, "20/20")
      Cells(1, 3).Value = res
     
    End Sub
    EXAMPLE OF DATA BASE

    name name W D a b Beta
    20/20 20/20 20 20 3 4 0.25
    25/25 25/25 25 25 5.211 4 0.25
    32/25 32/25 32 25 7.422 4 0.3151739
    32/28 32/28 32 28 9.633 4 0.28444444
    36/28 36/28 36 28 11.844 4 0.31640625
    38/25 38/25 38 25 0 0 0.3638196
    40/23 40/23 40 23 0 0 0.40312421
    40/25 40/25 40 25 0 2.85 0.37869822
    40/28 40/28 40 28 0 2.85 0.34602076
    40/32 40/32 40 32 0 2.85 0.30864198
    42/36 42/36 42 36 3.9 2.85 0.28994083
    45/18 45/18 45 18 3.9 2.85 0.51020408
    45/32 45/32 45 32 3.9 2.85 0.34154158
    45/36 45/36 45 36 3.9 2.85 0.30864198


    Thanks's for your help!!

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,745
    Articles
    77
    Blog Entries
    14
    Wow! From Switzerland coding in German, Spanish and English?

    I'll have a go at this tonight and see if I can make my way through it.
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    hahaha my parents are from Spain, thats why :P. It will be great if you could help me, I'm so lost , I really appreciate your help. Thank you very much and don't worry when ever you have time.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,745
    Articles
    77
    Blog Entries
    14
    Sorry, haven't been able to get to my computer over the weekend. I'm going to see if I can give this a go tonight/tomorrow night. (I've got a nutty couple of days... month... ahead. Bear with me though, I won't leave you hanging.)
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Great post ! I read the post . Thanks for sharing the idea about connecting a function with a database. Post more such creative idea . I like it.

Posting Permissions

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