Solved

EXCELL MYSQL ADD-IN, VB MACRO

Posted on 2013-12-29
31
327 Views
Last Modified: 2014-01-03
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
0
Comment
Question by:shaunwingin
  • 17
  • 14
31 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 39745195
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
0
 

Author Comment

by:shaunwingin
ID: 39745571
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.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39745631
Have you installed the Plugin? If so, How'd you go about it? eg what did you download
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:shaunwingin
ID: 39745710
It was installed as an MSI - see the link above please ...
0
 
LVL 43

Expert Comment

by:Rob
ID: 39745713
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
0
 

Author Comment

by:shaunwingin
ID: 39745958
tx!
0
 

Author Comment

by:shaunwingin
ID: 39747709
Say any luck yet with this?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39747711
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
0
 

Author Comment

by:shaunwingin
ID: 39747724
tx very much.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39748007
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?
0
 

Author Comment

by:shaunwingin
ID: 39748030
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...?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39748124
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

0
 

Author Comment

by:shaunwingin
ID: 39748158
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
0
 
LVL 43

Expert Comment

by:Rob
ID: 39748178
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")

...
0
 

Author Comment

by:shaunwingin
ID: 39748218
How can I have it take as parameters the value of a 2 cels instead ?
0
 

Author Comment

by:shaunwingin
ID: 39748220
Also - the conn string:

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

I'm using 5.5

Where is this driver installed?
0
 

Author Comment

by:shaunwingin
ID: 39748262
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?
0
 

Author Comment

by:shaunwingin
ID: 39748266
The procedure is actually stored in the MySQL db. Simply have to call it and pass the parameters...
0
 
LVL 43

Expert Comment

by:Rob
ID: 39748949
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
0
 

Author Comment

by:shaunwingin
ID: 39749506
Tx. Yes references always be the same
0
 
LVL 43

Expert Comment

by:Rob
ID: 39750185
Then you can set the parameters like this:

Param1 = sheet1.range("A1").value
Param2 = sheet1.range("B1").value
0
 
LVL 43

Expert Comment

by:Rob
ID: 39750188
Assuming they're on sheet1 in cells A1 and B1
0
 

Author Comment

by:shaunwingin
ID: 39750822
Tx. Its returning an error "Object required"
0
 
LVL 43

Expert Comment

by:Rob
ID: 39750875
Cam u show me where you've put that code?
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39750882
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

0
 

Author Comment

by:shaunwingin
ID: 39750883
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
0
 
LVL 43

Expert Comment

by:Rob
ID: 39750887
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
0
 

Author Closing Comment

by:shaunwingin
ID: 39750899
tx
0
 

Author Comment

by:shaunwingin
ID: 39753210
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
0
 
LVL 43

Expert Comment

by:Rob
ID: 39753295
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
0
 

Author Comment

by:shaunwingin
ID: 39753301
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question