Extract EagleML via Excel - How the Extract Works

The Extract functionality resides in the main Excel workbook.

About the Main Workbook

The main Excel workbook used in the extract process contains a tab for each of the supported EagleML object types. This workbook contains an Authorization section for login, a Profile section to configure the fields to be displayed in the extract result, and a set of Extract parameters on each worksheet.

The following is an example of the main workbook.

Each worksheet of the main workbook has the EXTRACT button, as shown below.

EXTRACT Process

Here's how the EXTRACT button works:

1. Clicking the EXTRACT button calls a main method (depending on the object type) to which the current worksheet corresponds, as shown in the following SMF object example.

   Sub GenerateSMF()
   CreateNewWorkbook ("SMFEXTRACT")
   End Sub

2. Each of these main methods calls a CreateNewWorkbook method with the appropriate feed type (SMFEXTRACT for SMF).
3. CreateNewWorkbook(feedType As String) method determines the name for the main worksheet for the newly created workbook and the name of the macro, which is then assigned to the Refresh button on the main worksheet of the newly created workbook.
4. A method then exports the VBA module of the main workbook and creates a new workbook, copying the contents of the worksheet on which the Extract button was clicked as the main (first) worksheet of the new workbook and removing the Profile section from this main worksheet and placing it on Profile (second) worksheet of newly created workbook.
5. A method then imports previously exported VBA module to the newly created workbook, changes the button name from EXTRACT to Refresh on the main worksheet, and assigns a macro (with a corresponding feed type passed as the parameter) to the Refresh button so that the extract process for this object type will be launched once the Refreshbutton is clicked.
   This macro has the following signature: Sub Extract_Data(feedType As String).
6. A method is finalized by prompting the user to save the newly created workbook with a Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) method and calling the Extract_Data macro with appropriate feed type from within the newly created workbook.

   The following is the method’s code:

   'Creates a new workbook, imports the current VBA module into it, assigns the macro to the button in the new workbook and launches it from within the new workbook for a specified feed type
   Sub CreateNewWorkbook(feedType As String)
   Dim macroName As String
   Dim sheetName As String
   Dim x As Worksheet
   Dim y As Worksheet
   If StrComp(feedType, "WRHSPOSITIONEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickWarehousePosition"
   sheetName = "Extract WarehousePosition"
   End If
   If StrComp(feedType, "WRHSTRADEEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickWarehouseTrade"
   sheetName = "Extract WarehouseTrade"
   End If
   If StrComp(feedType, "ENTITYEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickGenericEntity"
   sheetName = "Extract GenericEntity"
   End If
   If StrComp(feedType, "REFTIMESERIESEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickTimeSeries"
   sheetName = "Extract TimeSeries"
   End If
   If StrComp(feedType, "SCHEDULEEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickSchedule"
   sheetName = "Extract Schedule"
   End If
   If StrComp(feedType, "GENISSUEREXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickIssuer"
   sheetName = "Extract GenericIssuer"
   End If
   If StrComp(feedType, "SMFEXTRACT", vbTextCompare) = 0 Then
   macroName = "ClickSMF"
   sheetName = "Extract SMF"
   End If
   On Error Resume Next
   Kill (CurDir() & "\MrXL1.bas")
   'Export VBA module
   ActiveWorkbook.VBProject.VBComponents("Module1").Export (CurDir() & "\MrXL1.bas")
   Set x = Sheets(sheetName)
   Sheets(Array(sheetName)).Copy
   Workbooks(Workbooks.Count).Activate
   'Copy the Profile on the second sheet and delete it from the main (first) sheet
   Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Profile"
   fix_profile x, Sheets("Profile"), Sheets(sheetName)
   Sheets(sheetName).Select
   ActiveWorkbook.VBProject.VBComponents.Import (CurDir() & "\MrXL1.bas")
   Workbook_BeforeSave True, False
   Workbooks(Workbooks.Count).Activate
   'Assign macro to the button on the main sheet of the new workbook
   With Sheets(sheetName)
   .Shapes("Button 1").OnAction = "'" & ActiveWorkbook.Name & "'!" & macroName
   .Shapes("Button1").OnAction = "'" & ActiveWorkbook.Name & "'!" & macroName
   .Buttons("Button1").Caption = "Refresh"
   .Buttons("Button 1").Caption = "Refresh"
   End With
   Kill (CurDir() & "MrXL1.bas")
   
   'Launch the assigned macro
   Extract_Data (feedType)
   ActiveWorkbook.Save
   End Sub