Integration with VBA Macros
You may use the SaveToDB add-in in your VBA macros.
You may easily load data from database tables, views, stored procedures, from the web and text files.
You may easily save data changes back to Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite databases.
These features allow you to build VBA applications very quickly and to eliminate all the issues with database layers.
The programming model is simple:
- Configure SaveToDB add-in behavior in the design mode using all the SaveToDB features.
- Call SaveToDB methods from your VBA macros just like clicks on the ribbon buttons.
For example, use the following code to get reference to the SaveToDB add-in:
Dim addIn As COMAddIn Dim addInObj As Object Set addIn = Application.COMAddIns("SaveToDB") Set addInObj = addIn.Object
and use the following code to save changes back to a database:
addInObj.Save
Or use the code like this to load data from databases with new parameters:
Application.EnableEvents = False Range("Account").Value = 123 Range("Company").Value = "ABC" Application.EnableEvents = True addInObj.RebuildCommandTextFromNamedCells
See available VBA methods at
SaveToDB Methods for VBA Applications
VBA macros can call the SaveToDB add-in methods.
The methods are similar to clicks on the ribbon buttons like Save or Reload.
The methods work with tables that already exist on worksheets and can be processed using standard SaveToDB actions.
Use the following snippet to get the reference to the SaveToDB add-in:
Dim addIn As COMAddIn Dim addInObj As Object Set addIn = Application.COMAddIns("SaveToDB") Set addInObj = addIn.Object
Use the following snippets to save changes to a database and load data from the database after saving:
addInObj.Save addInObj.Save ListObject Call addInObj.Save(ListObject) If Not addInObj.Save(ListObject) Then MsgBox addInObj.LastResultMessage End If
Use the following snippets to load data and configuration from a database, the web, or a text file:
addInObj.Load addInObj.Load ListObject Call addInObj.Load(ListObject) If Not addInObj.Load(ListObject) Then MsgBox addInObj.LastResultMessage End If
Use the code like this to update query parameters in named cells at once and then to load data from databases, the web, or text files with new parameters:
Application.EnableEvents = False Range("Account").Value = 123 Range("Company").Value = "ABC" Application.EnableEvents = True addInObj.RebuildCommandTextFromNamedCells
Also, you may use direct calls to set parameter values starting SaveToDB 6.8:
addInObj.ParameterValue(ListObject, "Account") = 123 addInObj.ParameterValue(ListObject, "Company") = "ABC" Call addInObj.Load(ListObject)
This way does not require creating named cells. So it is universal. However, this method is not applicable prior SaveToDB 6.8.
You may find VBA examples in the SaveToDB Developer Guide 6.8 in the Wizards, Workbook Templates and Examples menu.
SaveToDB Methods for Using in VBA Applications
- ReadOnly Property LastResultMessage As String
- This property returns the result message of the last operation.
- ReadOnly Property Options As ISaveToDBOptions
- This property returns the object that allows reading and changing SaveToDB options.
See the ISaveToDBOptions interface declaration for details below.
The most of the options are available in the SaveToDB Options dialog box. - Function InsertConnectedListObject(TargetRange, ConnectionString, QueryObject, ObjectType) As Boolean
- The function inserts a connected ListObject object to the specified range like the Data Connection Wizard.
If TargetRange contains an existing ListObject object, it is replaced with a new one.
ObjectType types: TABLE, VIEW, PROCEDURE, FUNCTION, CODE, TEXT, and HTTP.
Pass correct database connection strings and existing database object names including schemas and names for types like a TABLE, VIEW, or PROCEDURE.
Note that you may use OLEDB, ODBC, and .NET providers. OLEDB and ODBC are supported by Excel while connections via .NET providers are supported by the SaveToDB add-in only.
Pass an existing file name or file name template with default values in the QueryObject for the TEXT type.
Pass an existing URL or URL template with default values in the QueryObject for the HTTP type.
You may find examples in the SaveToDB Developer Guide 6.8 in the Wizards, Workbook Templates and Examples menu. - Function Save(Optional ListObject) As Boolean
- The function saves table changes back to a database and reloads the data.
If a ListObject value is not specified, then the active table is used. - Function SavePivotTable(PivotTable) As Boolean
- The function saves changes of the underlying table back to a database and reloads the data.
The PivotTable table must have a data source as a database connected object like an editable table, view, or stored procedure. - Function SaveByMerge(Optional ListObject) As Boolean
- The function merges table data back to a database (inserts new rows and updates existing ones) and reloads the data.
If a ListObject value is not specified, then the active table is used. - Function GetSaveSQL(Optional ListObject) As String
- The function returns SQL commands that used to save table changes back to a database.
- Function GetSaveByMergeSQL(Optional ListObject) As String
- The function returns SQL commands that used to merge table data to a database.
- Function SaveByMergeDirect(ListObject, ConnectionString, QueryObject, ObjectType) As Boolean
- The function merges table data to a database.
You may merge data to a single table, or to multiple tables using stored procedures or SQL codes.
ObjectType types: TABLE, PROCEDURE, and CODE.
Pass correct database connection strings and existing database object names including schemas and names.
You may use OLEDB, ODBC, and .NET providers.
You may find examples in the SaveToDB Developer Guide 6.8 in the Wizards, Workbook Templates and Examples menu. - Function GetSaveByMergeDirectSQL(ListObject, ConnectionString, QueryObject, ObjectType) As String
- The function returns SQL commands generated for the SaveByMergeDirect function and has the same parameters.
- Function SaveAllSheetTables(Optional Worksheet) As Boolean
- The function saves changes of all worksheet tables back to a database and reloads the data.
If a Worksheet value is not specified, then the active worksheet is used. - Function SaveAllWorkbookTables(Optional Workbook, Optional UseWizard = True) As Boolean
- If a UseWizard value is true, then the function displays the Save Data Changes dialog box.
Otherwise, the function saves changes of all workbook tables back to a database and reloads the data. - Function Load(Optional ListObject, Optional ReloadMetadata = False, Optional IgnoreChanges = False) As Boolean
- The function reloads data and configuration.
Reloading configuration metadata may be disabled by setting ReloadMetadata to false.
If table data changes can be saved, then the save query dialog box is displayed.
You may suppress saving changes by setting IgnoreChanges to true. - Function LoadPivotTable(PivotTable, Optional ReloadMetadata = False, Optional IgnoreChanges = False) As Boolean
- The function reloads data and configuration for the PivotTable table and its underlying ListObject table.
Reloading configuration metadata may be disabled by setting ReloadMetadata to false.
If table data changes can be saved, then the save query dialog box is displayed.
You may suppress saving changes by setting IgnoreChanges to true. - Function LoadAllSheetTables(Optional Worksheet, Optional ReloadMetadata = False, Optional IgnoreChanges = False) As Boolean
- The function reloads data and configuration of all worksheet tables.
Reloading configuration metadata may be disabled by setting ReloadMetadata to false.
If table data changes can be saved, then the save query dialog box is displayed.
You may suppress saving changes by setting IgnoreChanges to true. - Function LoadAllWorkbookTables(Optional Workbook, Optional ReloadMetadata = False, Optional IgnoreChanges = False, Optional UseWizard = True) As Boolean
- If a UseWizard value is true, then the function displays the Reload Table Data dialog box.
Otherwise, the function reloads data and configuration of all workbook tables.
Reloading configuration metadata may be disabled by setting ReloadMetadata to false.
If table data changes can be saved, then the save query dialog box is displayed.
You may suppress saving changes by setting IgnoreChanges to true. - Function RebuildCommandTextFromNamedCells(Optional Worksheet) As Boolean
- The function updates query parameters from the named cells with the same names and reloads the data.
This method is used after updating multiple cells under Application.EnableEvents = False. - Function ReloadQueryList(Optional ListObject) As Boolean
- The function reloads QueryList object lists.
- Property ExcelConnectionString(ListObject) As String
- The property returns or sets ListObject ConnectionString property value in the Excel format like "OLEDB;<ConnectionString>" or "ODBC;<ConnectionString>".
The SaveToDB add-in allows using also .NET Providers using the form like "<ProviderName>;<ConnectionString>".
Do not forget to reload data after changes. - Property ProviderName(ListObject) As String
- The property returns or sets a ProviderName value of the ListObject ConnectionString property.
Do not forget to reload data after changes. - Property ConnectionString(ListObject) As String
- The property returns or sets a ConnectionString value of the ListObject ConnectionString property.
Do not forget to reload data after changes. - Property CommandText(ListObject) As String
- The property returns or sets a ListObject CommandText value.
Do not forget to reload data after changes. - Property QueryObject(ListObject) As String
- The property returns or sets a database object of the ListObject CommandText.
You may set values from GetQueryListItems lists that contain required information about database object types.
This function works like the ribbon Query List.
Do not forget to reload data after changes. - Property QueryList(ListObject) As String
- The property returns or sets the query list object related to ListObject that is used in the ribbon Query List.
- Property QueryLocked(ListObject) As Boolean
- The property returns or sets the lock state of the related QueryList object.
If the QueryList is locked, users cannot change the query using the ribbon QueryList list. - Function GetQueryListItems(ListObject) As Array
- The function returns a list of database objects loaded via the query list object.
- Function GetFields(ListObject) As Array
- The function returns a list of fields loaded from the ListObject data source.
- Function GetParameters(ListObject) As Array
- The function returns a list of parameters of the ListObject query.
The add-in detects stored procedure, function, and HTTP query parameters automatically.
You may add ribbon parameters for tables and views using the IsRibbonField property. - Property IsRibbonField(ListObject, FieldName) As Boolean
- The property returns or changes the current field state as a ribbon parameter.
This property works with tables and views only. - Function HasParameter(ListObject, ParameterName) As Boolean
- The function returns
true if the parameter exists and false if it is not.
For tables and views, this function returns true if the field is placed to the ribbon. - Property ParameterValue(ListObject, ParameterName) As Variant
- The property returns or sets the parameter value.
If the parameter contains a list of values as id and name pairs, this property returns or sets the id values. - Property ParameterRibbonValue(ListObject, ParameterName) As String
- The property returns or sets the parameter ribbon value.
If the parameter contains a list of values as id and name pairs, this property returns or sets the name values. - Function AddTableCursor(Optional ListObject) As Boolean
- The function adds a table cursor.
- Function RemoveTableCursor(Optional ListObject) As Boolean
- The function removes a table cursor.
- Function MoveNext(ListObject) As Boolean
- The function moves a table cursor to the next row.
The function returns false if the cursor is on the last row. - Function MovePrevious(ListObject) As Boolean
- The function moves a table cursor to the previous row.
The function returns false if the cursor is on the first row. - Function MoveFirst(ListObject) As Boolean
- The function moves a table cursor to the first row.
- Function MoveLast(ListObject) As Boolean
- The function moves a table cursor to the last row.
- Function InsertAddInSheets(Optional Workbook) As Boolean
- The function inserts worksheets required for the SaveToDB add-in.
- Function RemoveAddInSheets(Optional Workbook) As Boolean
- The function removes worksheets required for the SaveToDB add-in.
- Function ShowAddInSheets(Optional Workbook) As Boolean
- The function shows worksheets required for the SaveToDB add-in.
- Function HideAddInSheets(Optional Workbook) As Boolean
- The function hides worksheets required for the SaveToDB add-in.
- Function CleanAddInSheets(Optional Workbook) As Boolean
- The function clears worksheets required for the SaveToDB add-in.
Use this function to remove information about any object loaded before.
Reload data and configuration for all objects in a workbook to restore SaveToDB functionality. - Function RunCmd(Command) As Boolean
- The function executes the CMD command.
The working directory is the same as the active workbook directory. - Function CopyAndInsertRows(Range) As Boolean
- The function copies and inserts rows of the passed Range or a range of selected cells.
The result is the same as using the Copy & Insert Rows button at the Table Views group. - Function ActivateTab() As Boolean
- The function activates the SaveToDB tab.
IAddInUtilities Interface
The IAddInUtilities interface defines the methods implemented by the SaveToDB add-in.
You may use this methods from VBA macros and VSTO add-ins.
<ComVisible(True), Guid("CF0170F1-310E-421F-87DF-C177753EEDD5")> Public Interface IAddInUtilities ' Last Error Message ReadOnly Property LastResultMessage As String ' Options ReadOnly Property Options As IAddInOptions ' Ribbon Property FullRibbon() As Boolean Property RibbonTabVisible(Optional Workbook As Workbook = Nothing) As Boolean Function ActivateTab() As Boolean ' Execute Function ExecuteNonQuery(ConnectionString As String, CommandText As String) As Boolean Function ExecuteReader(ConnectionString As String, CommandText As String) As <MarshalAs(UnmanagedType.BStr)> System.Array Function RunCmd(Command As String) As Boolean ' Insert ListObject Function InsertConnectedListObject(TargetRange As Range, ConnectionString As String, QueryObject As String, ObjectType As String) As Boolean ' Save Function Save(Optional ListObject As ListObject = Nothing) As Boolean Function SavePivotTable(PivotTable As PivotTable) As Boolean Function SaveByMerge(Optional ListObject As ListObject = Nothing) As Boolean Function GetSaveSQL(Optional ListObject As ListObject = Nothing) As String Function GetSaveByMergeSQL(Optional ListObject As ListObject = Nothing) As String Function SaveByMergeDirect(ListObject As ListObject, ConnectionString As String, QueryObject As String, ObjectType As String) As Boolean Function GetSaveByMergeDirectSQL(ListObject As ListObject, ConnectionString As String, QueryObject As String, ObjectType As String) As String Function SaveAllSheetTables(Optional Worksheet As Worksheet = Nothing) As Boolean Function SaveAllWorkbookTables(Optional Workbook As Workbook = Nothing, Optional UseWizard As Boolean = True) As Boolean ' Load Function Load(Optional ListObject As ListObject = Nothing, Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean Function LoadPivotTable(PivotTable As PivotTable, Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean Function LoadAllSheetTables(Optional Worksheet As Worksheet = Nothing, Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False) As Boolean Function LoadAllWorkbookTables(Optional Workbook As Workbook = Nothing, Optional ReloadMetadata As Boolean = False, Optional IgnoreChanges As Boolean = False, Optional UseWizard As Boolean = True) As Boolean Function RebuildCommandTextFromNamedCells(Optional Worksheet As Worksheet = Nothing) As Boolean Function ReloadQueryList(Optional ListObject As ListObject = Nothing) As Boolean ' Connection Strings Property ExcelConnectionString(Optional ListObject As ListObject = Nothing) As String Property ConnectionString(Optional ListObject As ListObject = Nothing) As String Property PivotTableExcelConnectionString(Optional PivotTable As PivotTable = Nothing) As String Property PivotTableConnectionString(Optional PivotTable As PivotTable = Nothing) As String ' Connection String Properties Property ProviderName(Optional ListObject As ListObject = Nothing) As String Property PivotTableProviderName(Optional PivotTable As PivotTable = Nothing) As String ReadOnly Property ConnectionStringIntegratedSecurity(Optional ListObject As ListObject = Nothing) As Boolean Property ConnectionStringUserName(Optional ListObject As ListObject = Nothing) As String Property ConnectionStringPassword(Optional ListObject As ListObject = Nothing) As String ReadOnly Property PivotTableConnectionStringIntegratedSecurity(Optional PivotTable As PivotTable = Nothing) As Boolean Property PivotTableConnectionStringUserName(Optional PivotTable As PivotTable = Nothing) As String Property PivotTableConnectionStringPassword(Optional PivotTable As PivotTable = Nothing) As String ' Protecting Passwords and Usernames Function HideConnectionStringPassword(Optional ListObject As ListObject = Nothing) As Boolean Function HidePivotTableConnectionStringPassword(Optional PivotTable As PivotTable = Nothing) As Boolean Function SetConnectionStringPassword(Password As String, Optional ListObject As ListObject = Nothing) As Boolean Function SetPivotTableConnectionStringPassword(Password As String, Optional PivotTable As PivotTable = Nothing) As Boolean Function HideConnectionStringUserNameAndPassword(Optional ListObject As ListObject = Nothing) As Boolean Function HidePivotTableConnectionStringUserNameAndPassword(Optional PivotTable As PivotTable = Nothing) As Boolean Function SetConnectionStringUserNameAndPassword(UserName As String, Password As String, Optional ListObject As ListObject = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function SetPivotTableConnectionStringUserNameAndPassword(UserName As String, Password As String, Optional PivotTable As PivotTable = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean ' Protecting Passwords and Usernames at Worksheet and Workbook Levels Function HideWorksheetConnectionStringPasswords(Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function HideWorkbookConnectionStringPasswords(Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function HideWorksheetConnectionStringUserNamesAndPasswords(Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function HideWorkbookConnectionStringUserNamesAndPasswords(Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function SetWorksheetConnectionStringPasswords(Password As String, Optional Worksheet As Worksheet = Nothing) As Boolean Function SetWorkbookConnectionStringPasswords(Password As String, Optional Workbook As Workbook = Nothing) As Boolean Function SetWorksheetConnectionStringUserNamesAndPasswords(UserName As String, Password As String, Optional Worksheet As Worksheet = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean Function SetWorkbookConnectionStringUserNamesAndPasswords(UserName As String, Password As String, Optional Workbook As Workbook = Nothing, Optional ChangeIntegratedSecurity As Boolean = False) As Boolean ' Protecting Workbook Connections Function HideWorkbookConnectionPasswords(Optional Workbook As Workbook = Nothing) As Boolean Function HideWorkbookConnectionUserNamesAndPasswords(Optional Workbook As Workbook = Nothing) As Boolean Function SetWorkbookConnectionPasswords(Password As String, Optional Workbook As Workbook = Nothing) As Boolean Function SetWorkbookConnectionUserNamesAndPasswords(UserName As String, Password As String, Optional Workbook As Workbook = Nothing) As Boolean ' CommandText and QueryList Property CommandText(ListObject As ListObject) As String Property QueryObject(ListObject As ListObject) As String Property QueryList(ListObject As ListObject) As String Property QueryLocked(ListObject As ListObject) As Boolean Function GetQueryListItems(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array ' Fields and Parameters Function GetFields(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array Function GetParameters(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array Property IsRibbonField(ListObject As ListObject, FieldName As String) As Boolean Function HasParameter(ListObject As ListObject, ParameterName As String) As Boolean Property ParameterValue(ListObject As ListObject, ParameterName As String) As Object Property ParameterRibbonValue(ListObject As ListObject, ParameterName As String) As String ' Cursors Function AddTableCursor(Optional ListObject As ListObject = Nothing) As Boolean Function RemoveTableCursor(Optional ListObject As ListObject = Nothing) As Boolean Function DeleteTableCursor(Optional ListObject As ListObject = Nothing) As Boolean Function DeleteAllTableCursors(Optional Workbook As Workbook = Nothing) As Boolean Function MoveNext(Optional ListObject As ListObject = Nothing) As Boolean Function MoveToNext(Optional ListObject As ListObject = Nothing) As Boolean Function MovePrevious(Optional ListObject As ListObject = Nothing) As Boolean Function MoveToPrevious(Optional ListObject As ListObject = Nothing) As Boolean Function MoveFirst(Optional ListObject As ListObject = Nothing) As Boolean Function MoveToFirst(Optional ListObject As ListObject = Nothing) As Boolean Function MoveLast(Optional ListObject As ListObject = Nothing) As Boolean Function MoveToLast(Optional ListObject As ListObject = Nothing) As Boolean ' Form Fields Function AddFormFields(ListObject As ListObject, TopLeftCell As Range) As Boolean Function RemoveFormFields(ListObject As ListObject) As Boolean Function GetFormFieldCellName(ListObject As ListObject, ColumnName As String) As String ' Add-In Worksheets Function InsertAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean Function RemoveAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean Function DeleteAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean Function ShowAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean Function HideAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean Function CleanAddInSheets(Optional Workbook As Workbook = Nothing) As Boolean ' Table Views Function ApplyTableView(ListObject As ListObject, name As String) As Boolean Function SaveTableView(ListObject As ListObject, name As String) As Boolean Function DeleteTableView(ListObject As ListObject, name As String) As Boolean Function DeleteTableViews(ListObject As ListObject) As Boolean Function DeleteAllTableViews(Workbook As Workbook) As Boolean Function GetTableViews(ListObject As ListObject) As <MarshalAs(UnmanagedType.BStr)> System.Array Function CopyAndInsertRows(Optional Range As Range = Nothing) As Boolean End Interface
IAddInOptions Interface
The IAddInOptions interface defines the SaveToDB add-in options.
You may use this properties from VBA macros and VSTO add-ins.
<ComVisible(True), Guid("CF0170F1-310E-421F-87DF-C177753EEDD9")> Public Interface IAddInOptions ' Common Options Property DisplayLanguage As String Property DefaultDataLanguage As String Property WorkbookDataLanguage(Optional Workbook As Workbook = Nothing) As String Property EncryptPasswordsByDefault As Boolean Property EncryptWorkbookPasswords(Optional Workbook As Workbook = Nothing) As Boolean? Property ConnectionTimeout As Int32 Property CommandTimeout As Int32 Property GenerateSingleLineSQL As Boolean ' Excel Options Property ShowUrlMenu As Boolean Property ShowContextMenu As Boolean Property AutoActivateTableRelatedWindows As Boolean Property AutoArrangeWindows As Boolean Property AutoHideTaskbarMdiWindows As Boolean Property ShowDoubleClickCalendar As Boolean Property ShowDoubleClickPivot As Boolean Property AutoFilterRow As Boolean Property AutoOpenLastFile As Boolean Property AutoCleanBadNamedCells As Boolean ' Ribbon Options Property HideAdvancedMenuItems As Boolean Property ShowGroupAtDataTab As Boolean Property ShowGroupAtViewTab As Boolean Property ShowGroupAtDesignTab As Boolean Property ParameterHistoryLimit As Int32 ' Options Menu Property ShowCellEditor As Boolean Property ShowListEditor As Boolean ' Special Options Property TemplatePath As String Property ShowToolsMenu As Boolean? ' TaskPanes Property TaskPaneDockPosition As Int32 ' Cell Editor Property CellEditorDockPosition As Int32 Property CellEditorTop As Int32 Property CellEditorLeft As Int32 Property CellEditorHeight As Int32 Property CellEditorWidth As Int32 Property CellEditorDockHeight As Int32 Property CellEditorDockWidth As Int32 Property CellEditorFontSize As Single Property CellEditorFontName As String ' List Editor Property ListEditorDockPosition As Int32 Property ListEditorTop As Int32 Property ListEditorLeft As Int32 Property ListEditorHeight As Int32 Property ListEditorWidth As Int32 Property ListEditorDockHeight As Int32 Property ListEditorDockWidth As Int32 Property ListEditorFontSize As Single Property ListEditorFontName As String End Interface