Excel Addins
| http://www.savetodb.com/ | This Excel Addin facilitates writing data back to a database (as well as easily loading data from a database), the paid for ($150/pc) version allows use of SQL Server stored procedures, the free version allow less secure update of data. It can also store the formatting definition of an Excel ListObject (ie an Excel table) in it's own SQL server tables, along with query definitions etc, so that multiple users can put the ListObject in their worksheets and interact with the data it contains (locking rows and writing back to the database as necessary).
(My gut feeling is that it's very expensive for what it does, but the free version is very handy!)
| | | http://www.xlhub.com/ | Ditto.. but cheaper ($15/pc) but no where near as powerful as it mainly deals with writing back data to a database.
| | Excel SQL QueryTable Creation Wizard
AKA
SQL Addin
"Data Range Wizard" | The SQLaddin Addin is a wizard that can be used to create standard Excel SQL Query tables (known as data ranges).
Once created the QueryTable is saved with the workbook
and the Addin is no longer needed.
Microsoft provides the user a way to create these
QueryTables (without using VBA) but the process is horrible
(see RIBBON: Data>Get External
data>FromOtherSources>MS Query) -
I've still not worked out how to do it easily!
You can use standard MS interfaces to refresh or edit the properties of the QueryTable, simply right click on the range of data that was added as a QueryTable.
PowerQuery provides a lot of functionality over what QueryTables do, but for very simple things QueryTables are still very useful.
Using SQL QueryTables in Excel can often replace complex VBA macros that manipulate tables of data - which is beneficial as it can: - Be quicker
- Less errors – the SQL query is less prone to the usual VBA errors provided that the structure of the data does not change significantly
- Performance – the Jet.Oledb driver is much more efficient than any written VBA code with loops. Try any simple example
- Editable and refreshable like a PivotTable – The SQL Add-In will insert a query table into the worksheet. Which is editable and refreshable! That means that you refresh it just as you would with a PivotTable
| | | www.codevba.com | VBA Development environment toolbar addin - veyr handy | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
|
|