Solved

EXCELL MYSQL ADD-IN, VB MACRO

Posted on 2013-12-29
31
313 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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39745631
Have you installed the Plugin? If so, How'd you go about it? eg what did you download
0
 

Author Comment

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

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39750185
Then you can set the parameters like this:

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

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39750875
Cam u show me where you've put that code?
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE 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 42

Expert Comment

by:Rob Jurd, EE MVE
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 42

Expert Comment

by:Rob Jurd, EE MVE
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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 …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now