I haven't had much exposure to VBA or mySQL. I'm trying to create a custom function to query a mysql table and put the results into Excel.
The table contains CompanyName, Date and the invoice price. In the past, they've used an Excel table and used vlookups/filters to try and narrow it down but they've got about 30 years worth of data in an Excel sheet and sooner or later, it's going to break.
I've had them using the following sql query to a mysql database -
select * from StockManagement.Deliveries where CompanyName = "SupplierCode" and DATE BETWEEN "StartDate" and "EndDate"
However, now they want to use this as a function like =getinvoices(SupplierCode, Start Date, End Date) and for this to populate the the excel sheet with the 3 headers of the table, Company Name, Date, Invoice Amount
The code I've managed to string together to get the ODBC driver working and it actually pulling the data is as below. I just need to make this into a function where there are some variables.
Sub MySQLtoExcel()
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 = "StockManagement" ' Enter your database name here
User_ID = "root" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "SELECT * FROM StockManagment.Deliveries where CompanyName = 'ABC LTD' and DATE BETWEEN '2016-11-23' '2016-11-25';"
Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 5.3 ANSI 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:g500")
.ClearContents
.CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
