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
@Szag-Ot information is not yet sufficient?
– VHLeal
the database with which your script connects, is accessible from the other machine tb?
– Szag-Ot
@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) "
– VHLeal
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)"
– Szag-Ot
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.
– danieltakeshi
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.
– VHLeal
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
– Szag-Ot