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. 

The get started, download this Excel document with examples of the using the different SQL Spreads methods from VBA.

Methods
Events

Initializing the SQL Spreads VBA interface


To use the SQL Spreads API from VBA, declare the SQLSpreadsAPI object in the Workbook_Activate event handler in ThisWorkbook. 


Private Sub Workbook_Activate()
    Dim SQLSpreads As COMAddIn
    Set SQLSpreads = Application.COMAddIns("SQL Spreads")
    Set SQLSpreadsAPI = SQLSpreads.Object
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 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. 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 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 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

Feedback and Knowledge Base