EXCELL MYSQL ADD-IN, VB MACRO

Say,

I'm trying to write VB code to control this add- in ... any ideas?

http://www.mysql.com/why-mysql/windows/excel/edit/

The functionality would be to simply expand and execute the functions as required. Using the macro record function does not work as far as can tell. can this be done with VB code?

Using Excell 2010/ 2013
shaunwinginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
The record function won't work as you've experienced because it isn't using the excel front end but rather the add-in.

To be able to code it you'll need to create a reference to the plugin.  With the code editor open (alt+f11), go to Tools->References

on the popup window, find MySql, tick the box and click ok.

You'll now be able to use the object browser F2 and see the functions etc you can call
shaunwinginAuthor Commented:
Tx. I searched very carefully and MySql does not appear listed - tried to see if called something else - but couldn't see anything resembling it... Can you please help me trace it.
RobOwner (Aidellio)Commented:
Have you installed the Plugin? If so, How'd you go about it? eg what did you download
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

shaunwinginAuthor Commented:
It was installed as an MSI - see the link above please ...
RobOwner (Aidellio)Commented:
Thanks for confirming that. It's what I suspected but just easier to ask how you went about setting it up.  I'll set it up in my test system and get back to you
shaunwinginAuthor Commented:
tx!
shaunwinginAuthor Commented:
Say any luck yet with this?
RobOwner (Aidellio)Commented:
Not an empty promise my friend. Just trying to get some time on the pc. I'm on it in an hour or two at the most
shaunwinginAuthor Commented:
tx very much.
RobOwner (Aidellio)Commented:
Sorry it's taken so long.  As it is a COM add-in, it isn't visible to the vba scripting interface.  I thought it perhaps was a Excel Add-In (appears in the code editor).  

However, What you can do is use the Active X data objects library and access the database via ODBC. You will then be able to manipulate data as you need to.

What functions specifically are you wanting to execute?
shaunwinginAuthor Commented:
Tx. I need to execute a query with parameters i.e. user needs to provide 2 parameters and then have button to execute query.

The add-in allows for real time linking to the data and updating of data in a table.
This would be a nice to have.

Perhaps you can assist please...?
RobOwner (Aidellio)Commented:
You'd just think of them as two separate entities.  The add in gives you that nice real time linking to the data as well as in table updating.  

But it's easy to have vba code in the background take the two parameters via a function and use ODBC to execute the query.  This is an example from the mysql site on how to use the odbc connector to execute queries:

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-programming-vb-ado.html

Your example may look something like this:

Public Sub myodbc_ado_Click(param1 as String, param2 as String)
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
'connect to MySQL server using Connector/ODBC
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"
conn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'fetch the initial table ..
rs.Open "SELECT * FROM my_ado WHERE something='" + param1 + " AND something_else='" + param2 + "'", conn
' do somethign with the recordset
rs.Close
conn.Close

End Sub

Open in new window

shaunwinginAuthor Commented:
tx. Sounds a good plan
How can I pass parameter to the code?
I would think making a button using a textbox for user to click. can one make a buton with 2 text boxes superimposed on it?
Sorry I'm not too familiar with Exel VBA
RobOwner (Aidellio)Commented:
Your button calls the sub except move the param1 and param2 from being arguments to bring assigned via an input box

Public Sub myodbc_ado_Click()
Dim param1 as string
Dim param2 as string

Param1 = inputbox("parameter 1")
Param2 = inputbox("parameter 2")

...
shaunwinginAuthor Commented:
How can I have it take as parameters the value of a 2 cels instead ?
shaunwinginAuthor Commented:
Also - the conn string:

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"

I'm using 5.5

Where is this driver installed?
shaunwinginAuthor Commented:
I had a better look at the MySQL code and what we need is to call a Procedure (as far as can tell this is similar to a query, except can string queries and pass parameters).
Can this be done and how would one call it and pass it the parameters?
shaunwinginAuthor Commented:
The procedure is actually stored in the MySQL db. Simply have to call it and pass the parameters...
RobOwner (Aidellio)Commented:
You can have take two cell values. Will their reference always be the same? Eg A1?
What you want is even easier as you just use the execute function

...
Conn.Open
Conn.Execute "CALL myprocedure('"+param1+"', '"+param2+"');"
...

Open in new window


Once I know where your parameters come from and their references I can't showyou how to set them in the code
shaunwinginAuthor Commented:
Tx. Yes references always be the same
RobOwner (Aidellio)Commented:
Then you can set the parameters like this:

Param1 = sheet1.range("A1").value
Param2 = sheet1.range("B1").value
RobOwner (Aidellio)Commented:
Assuming they're on sheet1 in cells A1 and B1
shaunwinginAuthor Commented:
Tx. Its returning an error "Object required"
RobOwner (Aidellio)Commented:
Cam u show me where you've put that code?
RobOwner (Aidellio)Commented:
Better yet, here is an untested demo of what I would expect to see:

Public Sub myodbc_ado_Click(param1 as String, param2 as String)
	Dim conn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim fld As ADODB.Field
	Dim sql As String
	
	' Server settings for database
	Dim myserver as String
	Dim mydb as String
	Dim user as String
	Dim pwd as String
	
	' Parameters
	Dim param1 as String
	Dim param2 as String
	
	'connect to MySQL server using Connector/ODBC
	Set conn = New ADODB.Connection
	
	myserver = "localhost"
	mydb = "test"
	user = "venu"
	pwd = "venu"
	
	' get the parameters from the sheet
	Sheet1.Activate
	
	param1 = ActiveSheet.Range("A1").Value
	param2 = ActiveSheet.Range("B1").Value

	conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};"_
	& "SERVER="&myserver&";"_
	& " DATABASE="&mydb&";"_
	& "UID="&user&";PWD="&pwd&"; OPTION=3"
	conn.Open

	Conn.Execute "CALL myprocedure('"+param1+"', '"+param2+"');"

	conn.Close

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shaunwinginAuthor Commented:
Sub CreateTempTable()
The sheet is called Raw:
Dim conn As ADODB.Connection
Dim param1 As String
Dim param2 As String

'connect to MySQL server using Connector/ODBC
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 5.2 ANSI Driver};... hidden"
conn.Open

param1 = Raw.Range("C1").Value
param2 = Raw.Range("B3").Value
RobOwner (Aidellio)Commented:
ah ok... you have two ways of referencing the sheet, by the name (what you've tried to do) and by the object sheet[n].  You can see a list of the sheet objects sheet1,sheet2,sheet3 when in the code editor.

However, you are better off referencing by name.  To do this, use the Sheets object

eg

Sheets("Raw").Range(...).Value
shaunwinginAuthor Commented:
tx
shaunwinginAuthor Commented:
Say can I execute a macro to make the MySQL for Excel icon visible.
I tried this but fails:
Application.CommandBars("MySQL for Excel").Visible = True

How can I make the Raw sheet the displayed sheet?

Can the Call statement above return data from the query back to Excel and display in excel?

Please see this qu.:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28329710.html
RobOwner (Aidellio)Commented:
Say can I execute a macro to make the MySQL for Excel icon visible.
I'm not sure on that one.. might be worth asking another question on that.  I'd have to do some research and testing

How can I make the Raw sheet the displayed sheet?
You can activate the Raw sheet using Sheets("Raw").Activate in conjunction with Application.ScreenUpdating = False while the macro is running before setting it to True when the macro finishes.  This stops the workbook "flashing" with every action in the macro.

Can the Call statement above return data from the query back to Excel and display in excel?

It would depend on what the statement does.  If it calls a SELECT and returns data then yes.  If it just updates the database then no.  I'd need more info on what the statement does
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.