SQL Spreads VBA interface
SQL Spreads has a basic VBA interface/API where you can refresh and save data between Excel and SQL Server. You can also execute VBA code after the data has been refreshed from SQL Server.
To get started, download this Excel document with examples of using the different SQL Spreads methods from VBA.
Methods
Events
Initializing the SQL Spreads VBA interface
Private Sub Workbook_Activate()
Dim SQLSpreads As COMAddIn Set SQLSpreads = Application.COMAddIns("SQL Spreads") Set SQLSpreadsAPI = SQLSpreads.ObjectEnd SubSQL Spreads VBA methods
RefreshImportData
Description
Call to refresh the data from SQL Server into the spreadsheet.
The method will overwrite any unsaved changes in the document without showing any warning to the user. To check if there are unsaved database changes in the document, use the HasUnsavedChanges API method.
Example
' Refresh data from SQL Server SQLSpreadsAPI. RefreshImportDataHasUnsavedChanges
Description
Call to check if there are changes in the document that is not yet saved to the database.
If there are unsaved changes this method returns True, otherwise False.
Returns
HasUnsavedChanges Type: Boolean
Example
' Check if there are unsaved database changes in the document
If SQLSpreadsAPI.HasUnsavedChanges = True Then
MsgBox ("There are unsaved database changes in the document.")
End If
ExportDataToSQLServer
Description
Call this method to save the data in the spreadsheet to SQL Server.
Example
' Save the data to SQL Server
SQLSpreadsAPI. ExportDataToSQLServerToggleFilterPane
Description
Toggles the left-side SQL Spreads pane in Excel containing the Refresh and Save buttons and the Database Import filter.
Example
' Toggle the filter pane
SQLSpreadsAPI.ToggleFilterPane
EnableBeforeSaveEvent
Description
SQL Spreads can call a VBA method after the user has pressed the Save button, but before the data is saved to the database.
To enable calling of the BeforeSave event method, call the EnableBeforeSaveEvent method with the Enable parameter set to True.
Parameters
Name: Enable Type: Boolean
Example
' Enable EnableBeforeSaveEvent event
SQLSpreadsAPI.EnableBeforeSaveEvent True
Sub BeforeSave()
' This sub is called before data is saved to SQL Server
MsgBox ("Changes will now be saved to SQL Server.")
End Sub
EnableSaveCompletedEvent
Description
SQL Spreads can call a VBA method after the data is saved to the database.
To enable calling of the SaveCompleted event method, call the EnableSaveCompletedEvent method with the Enable parameter set to True.
Parameters
Name: Enable Type: Boolean
Example
' Enable SaveCompleted event
SQLSpreadsAPI.EnableSaveCompletedEvent True
' Example of the method that will be called after data is saved
Sub SaveCompleted()
' Data is now saved to the database
End Sub VBA event methods called by SQL Spreads
ImportDataCompleted
Description
If this method is defined in the VBA-project for a macro enabled Excel workbook with a SQL Spreads database connection, SQL Spreads will call this method after the data form SQL Server is imported into the Excel spreadsheet.
Example
Sub ImportDataCompleted()
' This sub is called after data is imported from SQL Server
End Sub
BeforeSave
Description
SQL Spreads can call a VBA method after the user has pressed the Save button, but before the data is saved to the database.
To enable calling of the BeforeSave method the EnableBeforeSaveEvent method must be called.
Example
' Enable EnableBeforeSaveEvent event
SQLSpreadsAPI.EnableBeforeSaveEvent True
Sub BeforeSave()
' This sub is called before data is saved to SQL Server
MsgBox ("Changes will now be saved to SQL Server.")
End SubDescription
SQL Spreads can call a VBA method after the data is saved to the database. To enable calling of the SaveCompleted method the EnableImportDataLoadedEvent method must be called.
Example
' Enable event SQLSpreadsAPI.EnableSaveCompletedEvent True ' Example of the method that will be called after data is saved to the database Sub SaveCompleted() ' Data is now saved to the databaseEnd Sub