Using VBA to pull data from mySQL to excel with Macro button

khelza

New Member
Joined
Feb 10, 2015
Messages
23
Hey everyone,

So I have a materials database made up in mySQL, which holds linked tables for materials, manufacturers and vendors.

I also have a Bill of Materials (BOM) in excel, in which there are empty columns for manufacturers, vendors, etc.

The goal is to have a Macro button that, once pressed, it will populate the BOM's empty columns with the appropriate data from the database.

I have very little experience with VBA and macros, so the information I have gathered is all from forums and the web.

In VBA, I have created a module for the connection to the database.

Code:
Sub ADOExcelSQLServer()     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '
     
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
     
    Server_Name = "localhost" ' Enter your server name here
    Database_Name = "database" ' Enter your database name here
    User_ID = "rooty" ' enter your user ID here
    Password = "nevergunnagiveyouup" ' Enter your password here
    SQLStr = "SELECT * FROM materials" ' Enter your SQL here
     
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
     
    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
End Sub

After getting a few debug errors, I am now able to run it without any errors. Although when I click run, nothing really seems to happen, but I am assuming a connection is being made behind the scenes?

*Not sure if it's useful to mention at this point, I have already set up the ODBC driver and successfully tested the connection to the db, as well as added ActiveX Data Object 2.8 to Reference in VBA

Back in excel, I added a macro button, and inserted the following code:

Code:
Private Sub CommandButton21_Click()

' Create a recordset object.
Dim rsMaterialsdb As ADODB.Recordset
Set rsMaterialsdb = New ADODB.Recordset


With rsMaterialsdb
    ' Assign the Connection object.
    .ActiveConnection = cnMaterialsdb
    ' Extract the required records.
    .Open "SELECT * FROM Manufacturers"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsMaterialsdb
    
    ' Tidy up
    .Close
End With


cnPubs.Close
Set rsMaterialsdb = Nothing
Set cnMaterialsdb = Nothing
                


End Sub


I seem to be getting an error at the .ActiveConnection line and not sure why.

Was wondering if someone could let me know if I have the right idea/heading in the right direction, and possibly, what might be wrong with my code?

Thanks! :rolleyes:
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top