Excel Add-On to display data from (My)SQL Database

Question

Hello,

is there an easy way of importing data from a mysql/odbc datasource into an excel spreadsheet?

The user should be able to select some values from drop downs (e..g date range, branch name etc.) and the values from the dropdown should be used to populate (prepared) SQL statements. The results should be displayed in the Excel file.

Ideally there would be a "save snapshot" menu item that would convert the dynamic excel file (with sql statements saved) as a static excel file thus reducing the exposure of internals (e.g. sql) to external ressources.

Thanks

Answer 1

http://sqlexcel.net/

Answer 2

You can download an ODBC driver for MySQL, create a data source, and use MS Query to create a custom SQL query that you can add to Excel via the Data menu.

Answer 3

Here is 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

Answer 4

Thanks. I Forgot about excel macros, that seems the way to go.

http://sqlexcel.net/ seems nice but I cannot see how to paste values from the excel sheet into the queries.

Answer 5

http://www.querycell.com <- my add-in

Answer 6

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