How to use the same supplement . xla on two machines on the same spreadsheet?

Asked

Viewed 99 times

2

I created a file with functions in VBA . xla that can be configured as excel add-on, but after using the functions in a spreadsheet any on my machine and sending the spreadsheet to someone else she can’t see the return of the function is that #NAME, Obs: xla has been configured on the host of the person I sent tbm.

Sub conectaBD()

    Dim MySQLdrive As String

    If (cn Is Nothing) Then
        Set cn = New ADODB.Connection
    End If

    If Not (cn.State = adStateOpen) Then

        MySQLdrive = Get_Driver()

        With cn
            .ConnectionString = "Driver={" & MySQLdrive & "};Server=" & Server_Name & ";DATABASE=" & DataBase & ";User=" & User_ID & ";Password=" & Password & ";Option=3;"
            .Open
        End With

    End If

End Sub

Function consultaValorPL(cod_Operacao As Integer, data As Date) As Double

    Dim SQLStr As String

    consultaValorPL = 0#

    SQLStr = "CALL listar_pu_por_data('" & Format(data, "yyyy-mm-dd") & "', " & cod_Operacao & ")"

    Call conectaBD

    Set rs = cn.Execute(SQLStr)

    If Not (rs.EOF) Then
        consultaValorPL = rs("pl").Value
    Else
        Exit Function
    End If


End Function

    Function Get_Driver() As String

    Const HKEY_LOCAL_MACHINE = &H80000002
    Dim l_Registry As Object
    Dim l_RegStr As Variant
    Dim l_RegArr As Variant
    Dim l_RegValue As Variant

    Get_Driver = ""
    Set l_Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    l_Registry.enumvalues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", l_RegStr, l_RegArr

    For Each l_RegValue In l_RegStr
        If InStr(1, l_RegValue, "MySQL ODBC", vbTextCompare) > 0 Then
            Get_Driver = l_RegValue
            Exit For
        End If
    Next
    Set l_Registry = Nothing
End Function

Thanks in advance for the help.

  • Post the contents of the.xla file so we can help with a possible cause. Or give more details on your question. Just the past information is not enough for someone to help you.

  • @Szag-Ot information is not yet sufficient?

  • the database with which your script connects, is accessible from the other machine tb?

  • @Szag-Ot yes, so much so that when I configure the supplement the formula works machine of the person, the problem is when I deliver a spreadsheet made by me and the person opens it, the formulas do not work and the formula is like this " ='H: GESTAO Manuela Folly Addin Fundos.xla'! see ValorPL($N$1;A424) "

  • 1

    I don’t know if I understand: at the time of forwarding it adds in the formula the path of the file, eh this? If it is, just in the original you already put the name of the.xla file and forward it together. Like this: "='Backgrounds.xla'! query)"

  • Is the name of the add-in the same as any other existing Sub or Function? Search for duplicates and make sure the Excel file is macros enabled.

  • Exactly @Szag-Ot, if I rename the formula the way you said it works, but what I didn’t want to give this rework to the person who receives the spreadsheet, because it would only do a little validation.

  • But you’re not going to give anyone a hard time if you make this change in your file. Just change and then send both files to the person who should save both in the same folder. That’s all

Show 3 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.