Excel Add-On to Display Data from (My) SQL Database
is there an easy way to import data from mysql / odbc datasource into Excel spreadsheet?
The user should be able to select some of the values from the dropdowns (date range, branch name, etc.) and the values from the dropdown should be used to populate the (prepared) SQL statements. The results should be displayed in an Excel file.
Ideally, there would be a Save Snapshot menu item that converts a dynamic excel file (with saved sql statements) as a static excel file, thereby reducing the exposure of internals (like sql) to external resources.
thank
You can download the MySQL ODBC driver , create a data source, and use MS Query to create your own SQL query, which you can add to Excel via the Data menu.
source to share
Here's an example:
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strCon = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MyDB;" _
& "User=UName;Password=PWord;Option=3;"
cn.Open strCon
strSQL = "SELECT * FROM Members"
rs.Open strSQL, cn
Worksheets(3).Cells(2, 1).CopyFromRecordset rs
source to share
This method should be faster than CopyFromRecordset. Of course you have to make sure you have the MySQL ODBC 5.1 driver installed ...
With AWorksheet.QueryTables.Add( _
Connection:="ODBC;Driver=MySQL ODBC 5.1 Driver;Port=3306;Server=mysqlservername;Database=snort;User=username;Password=password;Option=3;"
Destination:=AWorksheet.Range(AWorksheet.Cells(1, 1), AWorksheet.Cells(1, 15)) _
)
.CommandText = "SELECT Something FROM Somewhere"
.Name = "Name"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
.Delete
End With
source to share