How to Export Pivot Table along with Table Data using COM

Al_22614
Al_22614 Altair Community Member
edited June 2020 in Community Q&A
Hello All:

I am using Monarch COM automation and need to write an VBA script How to Export Pivot Table along with Table Data.  I have tried both EXPORTTABLE and JETEXPORTTABLE, as below, without success.  The Table is exported, but not the Pivot Table.

Option 1:  EXPFILE = MonarchObj.EXPORTTABLE (ExportFolder & ARFILE)
Option 2:  EXPFILE = MonarchObj.JEXPORTTable(ExportFolder & ARFILE, ARTBBL, 0)

Any help will be much appeciated.

Thanks,

------------------------------
Al Rice
------------------------------
Tagged:

Best Answer

  • Mahmoud
    Mahmoud
    Altair Employee
    edited June 2020 Answer ✓
    Hi Al,
    To export a Monarch Classic table to an Excel file with pivot, perform the following steps:
    Create an export in Monarch Classic,
    Save the model and the project file,
    you can run the project in the com object per following scripts:
    Make sure you add Monarch.exe to the Visual Studio project reference

    Dim MonarchObj As New Datawatch.Desktop.Automation.MonarchAutomationObject

    dim ok as boolean = MonarchObj.SetReportFile("c:\temp\Classic.prn", strPassword, bAppend)

    dim b1 as booleana = MonarchObj.SetModelFile("c:\temp\Lesson9.dmod")

    dim b2 as Boolean = MonarchObj.SetProjectFile("c:\temp\pivot.dprj")

    MonarchObj.RunAllExports()

    I tried the above script and the export contained two tabs, for was the table export and another one was the pivot.
    Regards
    Mo

     


    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-07-2020 05:44 PM
    From: Al Rice
    Subject: How to Export Pivot Table along with Table Data using COM

    Hello All:

    I am using Monarch COM automation and need to write an VBA script How to Export Pivot Table along with Table Data.  I have tried both EXPORTTABLE and JETEXPORTTABLE, as below, without success.  The Table is exported, but not the Pivot Table.

    Option 1:  EXPFILE = MonarchObj.EXPORTTABLE (ExportFolder & ARFILE)
    Option 2:  EXPFILE = MonarchObj.JEXPORTTable(ExportFolder & ARFILE, ARTBBL, 0)

    Any help will be much appeciated.

    Thanks,

    ------------------------------
    Al Rice
    ------------------------------
    "

Answers

  • Mahmoud
    Mahmoud
    Altair Employee
    edited June 2020 Answer ✓
    Hi Al,
    To export a Monarch Classic table to an Excel file with pivot, perform the following steps:
    Create an export in Monarch Classic,
    Save the model and the project file,
    you can run the project in the com object per following scripts:
    Make sure you add Monarch.exe to the Visual Studio project reference

    Dim MonarchObj As New Datawatch.Desktop.Automation.MonarchAutomationObject

    dim ok as boolean = MonarchObj.SetReportFile("c:\temp\Classic.prn", strPassword, bAppend)

    dim b1 as booleana = MonarchObj.SetModelFile("c:\temp\Lesson9.dmod")

    dim b2 as Boolean = MonarchObj.SetProjectFile("c:\temp\pivot.dprj")

    MonarchObj.RunAllExports()

    I tried the above script and the export contained two tabs, for was the table export and another one was the pivot.
    Regards
    Mo

     


    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-07-2020 05:44 PM
    From: Al Rice
    Subject: How to Export Pivot Table along with Table Data using COM

    Hello All:

    I am using Monarch COM automation and need to write an VBA script How to Export Pivot Table along with Table Data.  I have tried both EXPORTTABLE and JETEXPORTTABLE, as below, without success.  The Table is exported, but not the Pivot Table.

    Option 1:  EXPFILE = MonarchObj.EXPORTTABLE (ExportFolder & ARFILE)
    Option 2:  EXPFILE = MonarchObj.JEXPORTTable(ExportFolder & ARFILE, ARTBBL, 0)

    Any help will be much appeciated.

    Thanks,

    ------------------------------
    Al Rice
    ------------------------------
    "
  • Al_22614
    Al_22614 Altair Community Member
    edited June 2020
    Mahmoud said:

    Hi Al,
    To export a Monarch Classic table to an Excel file with pivot, perform the following steps:
    Create an export in Monarch Classic,
    Save the model and the project file,
    you can run the project in the com object per following scripts:
    Make sure you add Monarch.exe to the Visual Studio project reference

    Dim MonarchObj As New Datawatch.Desktop.Automation.MonarchAutomationObject

    dim ok as boolean = MonarchObj.SetReportFile("c:\temp\Classic.prn", strPassword, bAppend)

    dim b1 as booleana = MonarchObj.SetModelFile("c:\temp\Lesson9.dmod")

    dim b2 as Boolean = MonarchObj.SetProjectFile("c:\temp\pivot.dprj")

    MonarchObj.RunAllExports()

    I tried the above script and the export contained two tabs, for was the table export and another one was the pivot.
    Regards
    Mo

     


    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-07-2020 05:44 PM
    From: Al Rice
    Subject: How to Export Pivot Table along with Table Data using COM

    Hello All:

    I am using Monarch COM automation and need to write an VBA script How to Export Pivot Table along with Table Data.  I have tried both EXPORTTABLE and JETEXPORTTABLE, as below, without success.  The Table is exported, but not the Pivot Table.

    Option 1:  EXPFILE = MonarchObj.EXPORTTABLE (ExportFolder & ARFILE)
    Option 2:  EXPFILE = MonarchObj.JEXPORTTable(ExportFolder & ARFILE, ARTBBL, 0)

    Any help will be much appeciated.

    Thanks,

    ------------------------------
    Al Rice
    ------------------------------
    "
    Thanks Mo
    Your respond resolves my issue. Thanks again

    ------------------------------
    Al Rice
    ------------------------------
    -------------------------------------------
    Original Message:
    Sent: 06-08-2020 10:07 AM
    From: Mahmoud Abdolrahim
    Subject: How to Export Pivot Table along with Table Data using COM

    Hi Al,
    To export a Monarch Classic table to an Excel file with pivot, perform the following steps:
    Create an export in Monarch Classic,
    Save the model and the project file,
    you can run the project in the com object per following scripts:
    Make sure you add Monarch.exe to the Visual Studio project reference

    Dim MonarchObj As New Datawatch.Desktop.Automation.MonarchAutomationObject

    dim ok as boolean = MonarchObj.SetReportFile("c:\temp\Classic.prn", strPassword, bAppend)

    dim b1 as booleana = MonarchObj.SetModelFile("c:\temp\Lesson9.dmod")

    dim b2 as Boolean = MonarchObj.SetProjectFile("c:\temp\pivot.dprj")

    MonarchObj.RunAllExports()

    I tried the above script and the export contained two tabs, for was the table export and another one was the pivot.
    Regards
    Mo

     


    ------------------------------
    Mahmoud Abdolrahim
    Senior Implementation & Integration Engineer
    Altair Engineering
    ------------------------------

    Original Message:
    Sent: 06-07-2020 05:44 PM
    From: Al Rice
    Subject: How to Export Pivot Table along with Table Data using COM

    Hello All:

    I am using Monarch COM automation and need to write an VBA script How to Export Pivot Table along with Table Data.  I have tried both EXPORTTABLE and JETEXPORTTABLE, as below, without success.  The Table is exported, but not the Pivot Table.

    Option 1:  EXPFILE = MonarchObj.EXPORTTABLE (ExportFolder & ARFILE)
    Option 2:  EXPFILE = MonarchObj.JEXPORTTable(ExportFolder & ARFILE, ARTBBL, 0)

    Any help will be much appeciated.

    Thanks,

    ------------------------------
    Al Rice
    ------------------------------
    "

Categories