Guide Section: Excel Desktop Client
Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference

This article is a list of details relevant to EDC spreadsheets:

• Activate a spreadsheet for EDC
• Only recent Excel versions are supported
• Add VBA to Capture Excel→Save and Excel→Close
• Add custom VBA to run on Excel Client→Open and Excel Client→Save
• An imported Excel Client EASAP name must be unique
• Excel Named Ranges must not have 'prop_' as a prefix
• Run DATABASE TO EXCEL DESKTOP without a CASE VIEWER
• Modify milliseconds before edc.xlsm times out

The objects below have specific roles in an Excel Client EASAP.

• CASE VIEWER
• FILTER
• EXTERNAL FILTER
• DATABASE TO EXCEL DESKTOP
• SET CASE STATUS
• EXCEL SAVED
• SET CUSTOM ID

To make any of these objects active an Author must:

• Upload a spreadsheet and
• Add a SHEET under SPREADSHEET LIST
• For this SHEET set
  File: (the spreadsheet filename)
  Desktop: TRUE

For a completed example and the steps to create an EDC version of Mortgage Calculator EASAP see Author a simple EDC EASAP

Please update to a more recent version if you are running an Excel version that is older than: Excel 2010 14.0.7165.5000

To hide proprietary formulas and VBA from the User an Excel Client an Author should Implement locked-mode for an Excel Client spreadsheet

A customer may run their own VBA:

• Initially when an Excel Client is first opened by implementing a module and function called: EASA.onEDCOpen
• Just before an EASA Ribbon > Save writes to the database by implementing a module and function called: EASA.onEDCSave

• A customer may wish to add VBA (below) to ignore clicking the 'EASA Ribbon > Save' button
  • ( onBeforeEASASave = True will cancel any User-attempt to 'EASA Ribbon > Save')
  • Usage and functionality follow https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.beforesave

Function onBeforeEASASave() As Boolean
    ' ...(optional) customer code
    onBeforeEASASave = True
End Function

The following VBA will ask an EDC User if he or she would like to save the case record before closing the Excel Client instance.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  If Sheets("EASA").Range("EASA") <> "" Then
    If MsgBox?("Do you want to save your CASE before closing ?", vbYesNo, "CASE") = vbYes Then
      ThisWorkbook?.Unprotect Pass
      Application.Run "Easa.xlamEasaRibbon.Save", a
      ThisWorkbook?.Saved = True
      Application.Run "Easa.xlamEasaRibbon.ProtectClean?"
    Else
      ThisWorkbook?.Unprotect Pass
      ThisWorkbook?.Saved = True
      Application.Run "Easa.xlamEasaRibbon.ProtectClean?"
    End If
  End If
End Sub 

An imported Excel Client EASAP name will be incremented with an integer in parenthesis if one of the same name already exists.

Excel Client EASAP's cannot be deleted in the traditional sense as the data viewable in a CASE VIEWER is persistent by design.

Due to some unfortunate internal behavior of Excel, any named range with a prefix ' prop_ ' in any combination of capital or lowercase letters will cause the complete loss of data integrity within that range.

For an Excel Client spreadsheet rename any named range with the prefix ' prop_ ' to ' my_prop_ ' or ' property_ ', etc…

In order to download an Excel Client instance without using a CASE VIEWER add this javascript to the Label: of a LABEL.

An EDC EASAP will allow the edc.xlsm file may only be opened and used for 90 seconds after it has been downloaded.

This default may be modified in: <SERVERDATA>\jsf-easa\admin\config\context.properties change ' edc.automaticOpen.timeout=90000 '

Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference