Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The following is an example of the main workbook.Image Removed

...

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

...

EXTRACT Process

Here's how the EXTRACT button works:

  1. Clicking

...

  1. 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.

    Code Block
    Sub GenerateSMF()
    CreateNewWorkbook ("SMFEXTRACT")
    End Sub
  2. Each of these main methods calls

...

  1. CreateNewWorkbook

...

  1.  method with the appropriate feed type (SMFEXTRACT for SMF).

  2. CreateNewWorkbook(feedType As String)

...

  1.  method determines the name for the main worksheet for the newly created workbook and the name of the macro, which is then assigned to

...

  1. the Refresh

...

  1.  button on the main worksheet of the newly created workbook.

  2. A method then exports the VBA module of the main workbook and creates a new workbook, copying the contents of the worksheet on which

...

  1. the Extract

...

  1.  button was clicked as the main (first) worksheet of the new workbook and removing the Profile section from this main worksheet and placing it

...

  1. on Profile (second) worksheet of newly created workbook.

  2. A method then imports previously exported VBA module to the newly created workbook, changes the button name

...

  1. from EXTRACT to Refresh on the main worksheet, and assigns a macro (with a corresponding feed type passed as the parameter) to

...

  1. the Refresh

...

  1.  button so that the extract process for this object type will be launched once

...

  1. the Refreshbutton is clicked.
    This macro has the following signature: Sub Extract_Data(feedType As String).

  2. 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:

    Code Block
    '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