Excel vba how to pass range to a function retrieve data from mysql

I write a vba to pass the offset of range as input of query table of another function but i encounter error.

Sub test2()

Dim LastCol As Long
With ThisWorkbook.Sheets("Summary (AIX)")
LastCol = .Range("A2").SpecialCells(xlCellTypeLastCell).Column
End With

Dim r As Long
Dim ser_name As String
Dim y As String

For r = (LastCol - 1) To 2 Step -2
With ThisWorkbook.Sheets("Summary (AIX)")
y = Cells(2 + 2, r + 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

MsgBox CStr(Cells(2, r).Value)
ser_name = CStr(Cells(2, r).Value)
get_unix_avg_cpu ser_name, y
End With
Next r
End Sub

=========================================================================

Sub get_unix_avg_cpu(server_hostname, x)


Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String


Dim qt As QueryTable
ThisWorkbook.Sheets(server_hostname).Activate

ConnString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=123456;Option=3;"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open


SQL = "SELECT cpu_max_unix_0.CPU FROM test.cpu_max_unix cpu_max_unix_0 where cpu_max_unix_0.SERVER_NAME='" & server_hostname & "' Order By cpu_max_unix_0.LOGDATE"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = ThisWorkbook.Sheets("Summary (AIX)").QueryTables.Add(Connection:=oRS, _
Destination:=ThisWorkbook.Sheets("Summary (AIX)").Range(x))


qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing


End Sub

Stop on "Sub get_unix_avg_cpu(server_hostname, x)" Compile error :User defined type not defined

Please help!!

Answers


ADODB is an external library, that means that it isn't built into Excel, so you need to add it to your project.

To do this, in the IDE click Tools > References

Then scroll down until you find "Microsoft ActiveX Data Objects", there'll be a few of these so pick the one with the highest number, tick it and then press ok.

You should then be able to run your code.

This approach is known as Early Binding and for your workbook to work for other people they will also need this reference installed on their computer. In general, when developing it is best to use the lowest version of the library that your code will run on. You could also use late binding.


Need Your Help

Node.js stack trace info

node.js mongoose stack-trace

I'm looking for info on Node.js error stacks, and why layers seems to be missing from them. We are throwing an error when a user is not found in our Mongo database. We are using Mongoose as an ODM....

write java interface

java automated-tests

as I declare a table of strings in all java testss and functions , is it possible de declare it once in an interface and to call it anywhere ?