Can I Use VBA Macro in VyNDEX?

KB#: 000015460

Last Updated: 6/Jan/2020

If your VyNDEX version is Ver. 1.3. or later, you can pull, push and clear records by using VBA macro. Here's how the macro works for pull, push and clear records. Please make sure to confirm how they work and use the macro on your own responsibility.

 Pull

• The data is pulled automatically when you open the Excel sheet (book),
• The macro behaves exactly same as you run Pull command in VyNDEX add-in.
• The macro doesn't do anything for any sheet which is not created in VyNDEX.

Private Sub Workbook_Open()

    Dim vyndex As Object
    Dim hr As Long
    Dim ws As Worksheet
  
    Set vyndex = Application.COMAddIns("VyNDEX").Object
    
    For Each ws In ThisWorkbook.Worksheets
        hr = vyndex.Load(ws)
        Debug.Print ws.Name + " : " + vyndex.GetConstant(hr)
    Next ws

End Sub

 Push

• The macro behaves exactly same as you run Push command in VyNDEX add-in.
• The macro doesn't do anything for any sheet which is not created in VyNDEX.

Sub UpdateAllSheets()

    Dim vyndex As Object
    Dim hr As Long
    Dim ws As Worksheet
    
    Set vyndex = Application.COMAddIns("VyNDEX").Object
    
    For Each ws In ThisWorkbook.Worksheets
        hr = vyndex.Update(ws)
        Debug.Print ws.Name + " : " + vyndex.GetConstant(hr)
    Next ws

End Sub

Clear Records

• The macro behaves exactly same as you run Clear Records command in VyNDEX add-in.
• The macro doesn't do anything for any sheet which is not created in VyNDEX.

Sub ClearRecordsAllSheets()

    Dim vyndex As Object
    Dim hr As Long
    Dim ws As Worksheet
    
    Set vyndex = Application.COMAddIns("VyNDEX").Object
    
    For Each ws In ThisWorkbook.Worksheets
        hr = vyndex.ClearRecord(ws)
        Debug.Print ws.Name + " : " + vyndex.GetConstant(hr)
    Next ws

End Sub

• Load, Update and ClearRecord methods return following values:
  • 0x00000000: Successful Completion
  • 0x00000001: 0 Item Processed
  • 0x00000002: Cancel button, Log-in Cancelled 
  • 0x00000003: Hidden Target Sheet 
  • 0x00000004: Sheet Protected
  • 0x00000005: Sheet Inaccessible
  • 0x00000006: Filter Configured in the Sheet
  • 0x80004005: Unexpected Error
  • 0x80004006: Mismatched Versions
  • 0x80004004: Error Occurred in Salesforce
  • 0x80004007: Activation Error
  • 0x80004008: System Item Occurrence Check Error