Using SQL Spreads with VBA

SQL Spreads VBA API is available in SQL Spreads Premium and above

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.

Methods

• RefreshImportData
• HasUnsavedChanges
• ExportDataToSQLServer
• ToggleFilterPane
• EnableBeforeSaveEvent
• EnableSaveCompletedEvent

Events

• ImportDataCompleted
• BeforeSave
• SaveCompleted

Initializing the SQL Spreads VBA interface

To use the SQL Spreads API from VBA, add the following code to ThisWorkbook:

Private Sub Workbook_Activate()
    ' Initiate the SQL Spreads VBA API
    InitiateSQLSpreads
End Sub

' This sub initiates the SQL Spreads VBA API
Public Sub InitiateSQLSpreads()
    ' Check if already initiated
    If SQLSpreadsAPI Is Nothing Then
        Dim SQLSpreads As COMAddIn
        Set SQLSpreads = Application.COMAddIns("SQL Spreads")
        Set SQLSpreadsAPI = SQLSpreads.Object
    End If
End Sub

It is good practice to call the InitiateSQLSpreads method before you call any of the SQL Spreads API methods to be sure that the reference is initiated, as in the example below:

Sub RefreshFromDatabase_Click()    
    ' Check if the SQL Spreads AddIn is initiated and re-initiate if needed
    ThisWorkbook.InitiateSQLSpreads
    
    ' Refresh data from SQL Server
    SQLSpreadsAPI.RefreshImportData
End Sub

SQL 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.RefreshImportData

HasUnsavedChanges

Description

Call to check if there are changes in the document that are 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.ExportDataToSQLServer

ToggleFilterPane

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 from 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 Sub

SaveCompleted

Description

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 database
End Sub