macros to query a database

T

tannu

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
 
T

tannu

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
Click to expand...

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

tannu

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.
Click to expand...

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
 
D

Dick Kusleika

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.
Click to expand...

In Excel, Data > Get External Data > New Database Query.
 
G

gimme_this_gimme_that

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
 
T

tannu

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
Click to expand...

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
 
G

gimme_this_gimme_that

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.
 
T

tannu

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").ListIndex

Thank you for your help.

Thanks
 
G

gimme_this_gimme_that

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update MySQL database using Excel VBA 1
putting criteria directly into a database query, dsum, daverage, e 2
Excel, Macros, Database 1
How to read index value and selected text of dropdown excel in C# 1
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
Word to Excel: Export comments with text and line number 0
Trying to recover deleted macros 0
how to re-sign macros afted editing them? 1

Top