Hi

Can someone help me with macros to query a database.

Example:
I have a excel sheet with data which i get from querying a database.

query for example:
select host_name,server_vendor,architecture,project,owner from servers
where owner=test123 and architecture='64'


Every time the excel sheet is outdated I have to query a database and
update excel sheet.

So I am lookig for macros which automatically query databse and get
all the information and update the excel sheet.

Appreciate your help.

Thanks

On Feb 24, 2:18*pm, tannu wrote:
Hi

Can someone help me with macros to query a database.

Example:
I have a excel sheet with data which i get from querying a database.

query for example:
select host_name,server_vendor,architecture,project,owner from servers
where owner=test123 *and architecture='64'

Every time the excel sheet is outdated I have to query a database and
update excel sheet.

So I am lookig for macros which automatically query databse and get
all the information and update the excel sheet.

Appreciate your help.

Thanks

In others words
Is it possible to pick up information from a MySQL database using a
macro in Excel?

On Tue, 24 Feb 2009 18:05:18 -0800 (PST), tannu
wrote:


In others words
Is it possible to pick up information from a MySQL database using a
macro in Excel?

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Install the ODBC driver for MySQL and use Data Get External Data to create
a query table on your Excel sheet.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Feb 25, 7:35*am, Dick Kusleika wrote:
On Tue, 24 Feb 2009 18:05:18 -0800 (PST), tannu
wrote:



In others words
Is it possible to pick up information from a MySQL database using a
macro in Excel?

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Install the ODBC driver for MySQL and use Data Get External Data to create
a query table on your Excel sheet.
--
Dick Kusleika
Microsoft MVP-Excelhttp://www.dailydoseofexcel.com

Thanks for your reply
I installed "mysql-connector-odbc-5.1.5-win32.msi" on my windows box.
Also i have mysql query browser from where I can query database and
save the results in excel form.
I need the code( macros) that is just enabling the macros should
automatically query the databse and updat the excel sheet.

Can you please let me know.
Thanks

On Wed, 25 Feb 2009 16:11:08 -0800 (PST), tannu
wrote:

Thanks for your reply
I installed "mysql-connector-odbc-5.1.5-win32.msi" on my windows box.
Also i have mysql query browser from where I can query database and
save the results in excel form.
I need the code( macros) that is just enabling the macros should
automatically query the databse and updat the excel sheet.


In Excel, Data Get External Data New Database Query.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Option Explicit

Global m_connection As ADODB.connection
Global fileNo As Integer

Sub getRegions()
Dim thisYear As String
Dim sheetName As String
Dim sql As String
Dim rs As Integer
sql = "select host_name,server_vendor,architecture,project,owner "
& _
"from servers where owner=test123 and architecture='64' "
sheetName = "Sheet 1"
OpenConnection
rs = selectData(sql, sheetName)
CloseConnection
End Sub

Public Sub OpenConnection()
Dim dbsource As String
Dim username As String
Dim password As String
Select Case Sheets("Home").DropDowns
("sg_database_dropdown").ListIndex
Case Else
dbsource = "MySQL connection information"
End Select
username = "myusername"
password = "mypassword"
Set m_connection = CreateObject("ADODB.Connection")
m_connection.CommandTimeout = 2000
m_connection.Open dbsource, username, password

End Sub

Public Sub CloseConnection()
m_connection.Close
End Sub

Public Sub doSql(sql As String)
Dim Command As Command
If 0 = fileNo Then
Set Command = CreateObject("ADODB.Command")
Set Command.ActiveConnection = m_connection
Command.CommandText = sql
Call Command.Execute
Set Command = Nothing
Else
Print #fileNo, sql & ";" & Chr(10)
End If
End Sub

'Function fixSingleQuotes(s As String) As String
' Dim oRegExp As RegExp
' Set oRegExp = New RegExp
' oRegExp.IgnoreCase = True
' oRegExp.Global = True
' oRegExp.Pattern = "'"
' fixSingleQuotes = oRegExp.Replace(s, "''")
'End Function

Public Function selectData(sql As String, sheetName As String) As
Integer
Dim count As Integer
Dim Command As ADODB.Command
Set Command = CreateObject("ADODB.Command")

Set Command.ActiveConnection = m_connection
Command.CommandTimeout = 2000
Command.CommandType = adCmdText
Command.CommandText = sql

Dim sheet As Worksheet
Set sheet = ThisWorkbook.Sheets(sheetName)
sheet.Visible = True
sheet.Cells.Clear

Dim rs As ADODB.Recordset
Set rs = Command.Execute

Dim field As ADODB.field
Dim fieldIndex As Integer
fieldIndex = 1
For Each field In rs.Fields
sheet.Cells(1, fieldIndex).Value = field.Name
fieldIndex = fieldIndex + 1
Next field

Dim colCount As Integer
colCount = rs.Fields.count

Dim rowIndex, index As Integer
rowIndex = 2
count = 0
While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
For index = 1 To colCount
sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
Next index
rowIndex = rowIndex + 1
count = count + 1
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

Set Command = Nothing
Sheets(sheetName).Select
Range("A1").Select

selectData = count
End Function

On Feb 26, 4:40*pm, "
wrote:
Option Explicit

Global m_connection As ADODB.connection
Global fileNo As Integer

Sub getRegions()
* * Dim thisYear As String
* * Dim sheetName As String
* * Dim sql As String
* * Dim rs *As Integer
* * sql = "select host_name,server_vendor,architecture,project,owner "
& _
* * * * "from servers where owner=test123 *and architecture='64' "
* * sheetName = "Sheet 1"
* * OpenConnection
* * rs = selectData(sql, sheetName)
* * CloseConnection
End Sub

Public Sub OpenConnection()
* * Dim dbsource As String
* * Dim username As String
* * Dim password As String
* * Select Case Sheets("Home").DropDowns
("sg_database_dropdown").ListIndex
* * Case Else
* * * *dbsource = "MySQL connection information"
* * End Select
* * username = "myusername"
* * password = "mypassword"
* * Set m_connection = CreateObject("ADODB.Connection")
* * m_connection.CommandTimeout = 2000
* * m_connection.Open dbsource, username, password

End Sub

Public Sub CloseConnection()
* * m_connection.Close
End Sub

Public Sub doSql(sql As String)
* * Dim Command As Command
* * If 0 = fileNo Then
* * * * Set Command = CreateObject("ADODB.Command")
* * * * Set Command.ActiveConnection = m_connection
* * * * Command.CommandText = sql
* * * * Call Command.Execute
* * * * Set Command = Nothing
* * Else
* * * * Print #fileNo, sql & ";" & Chr(10)
* * End If
End Sub

'Function fixSingleQuotes(s As String) As String
' * *Dim oRegExp As RegExp
' * *Set oRegExp = New RegExp
' * *oRegExp.IgnoreCase = True
' * *oRegExp.Global = True
' * *oRegExp.Pattern = "'"
' * *fixSingleQuotes = oRegExp.Replace(s, "''")
'End Function

Public Function selectData(sql As String, sheetName As String) As
Integer
* *Dim count As Integer
* *Dim Command As ADODB.Command
* *Set Command = CreateObject("ADODB.Command")

* *Set Command.ActiveConnection = m_connection
* *Command.CommandTimeout = 2000
* *Command.CommandType = adCmdText
* *Command.CommandText = sql

* *Dim sheet As Worksheet
* *Set sheet = ThisWorkbook.Sheets(sheetName)
* *sheet.Visible = True
* *sheet.Cells.Clear

* *Dim rs As ADODB.Recordset
* *Set rs = Command.Execute

* *Dim field As ADODB.field
* *Dim fieldIndex As Integer
* *fieldIndex = 1
* *For Each field In rs.Fields
* * * sheet.Cells(1, fieldIndex).Value = field.Name
* * * fieldIndex = fieldIndex + 1
* *Next field

* *Dim colCount As Integer
* *colCount = rs.Fields.count

* *Dim rowIndex, index As Integer
* *rowIndex = 2
* *count = 0
* *While Not rs Is Nothing And Not rs.BOF And Not rs.EOF
* * * For index = 1 To colCount
* * * * *sheet.Cells(rowIndex, index).Value = rs(index - 1).Value
* * * Next index
* * * rowIndex = rowIndex + 1
* * * count = count + 1
* * * rs.MoveNext
* *Wend

* *rs.Close
* *Set rs = Nothing

* *Set Command = Nothing
* *Sheets(sheetName).Select
* *Range("A1").Select

* *selectData = count
End Function

Thank you so much for your reply.
I get compile error as "User-defined type not defined" for "Global
m_connection As ADODB.Connection"
I think ADODB is not the correct type.
Can you please help me with this.
Thanks

From Excel:

Alt-F11 : This takes you to the VBA IDE

From the top menu:

Tools-References

Check these available references - it's okay if your version numbers
are different, check the most recent one

Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

The last one is the library that has the ADOBDB stuff in it.

Once you've registered these libraries with your Excel Workbook you
won't get this error anymore.

Thank you for your reply.
I am getting "Run Time error '9' - Subscript out of range" when i get
to the line
Select Case Sheets("Home").DropDowns("sg_database_dropdown").L istIndex

Thank you for your help.

Thanks

Ummm.

In my Workbook I have a dropdown that allows the user to select either
a development or production database. The dropdown is on a sheet named
Home.

Here do this:

The first thing you need to get is your MySql connection string.

To get that create a file in some folder, say your C: drive.

Give it any name but give it a udl suffix.

Such as C:\mysql.udl

Go into IE file Explorer and right click on that file.

Fill out all the information on all the tabs.

Then mysql.udl will have the connection string.

Edit mysql.udl with NotePad - copy and paste.

Then swap out the definition of OpenConnection() that I gave you with
my customizations
and swap it for this version:


Public Sub OpenConnection()
Dim dbsource As String
Dim username As String
Dim password As String
dbsource = "your udl connection string"
username = "your mysql username"
password = "your mysql password"
Set m_connection = CreateObject("ADODB.Connection")
m_connection.CommandTimeout = 2000
m_connection.Open dbsource, username, password

End Sub