Extract EagleML via Excel - How the Extract Works

Owned by Natalie Gesin (Deactivated)
Last updated: Oct 25, 2024 by Kinga Huszno
3 min read

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