2 Replies Latest reply: May 15, 2014 9:57 AM by John Fitzgerald RSS

    Formats and Appending Using JetExportTable to Excel

    John Fitzgerald

      I'm learning to use the OLE programming methods with Monarch (v6 Pro) and having a few minor issues using the JetExportTable() method.  First, when the data was sent to Excel, the formatting of the fields (i.e. 2 decimal places, commas between Thousands) was not preserved.  In Monarch under "Folders and File Types" I have selected Excel 8.0, and under "Export and Print Options" I've got the "Apply formatting to XLS files" checked - which I thought would be the settings used by the method.

       

      Using MonarchObj.JetExportTable("CompanyName.xls", "July22", 1)[/b] created the worksheet in the Excel file, but without formatting.

       

      Trying to get around that, I created a table in Excel called "July22", formatted the cells, and then tried to do it as an append using  MonarchObj.JetExportTable("CompanyName.xls", "July22", 2)[/b] .  This time, it created a new table called "July221", again without the applied formatting.  Just for kicks, I tried running the macro again, and it DID append the data to the "July221" table.

       

      Could someone tell me if it is possible to preserve formatting of data into Excel using JetExportTable, and also what I can do to properly append data?

       

      Thanks!

        • Formats and Appending Using JetExportTable to Excel
          Gareth Horton

          John

           

          This works just fine for me, I assume that you have excel installed on the machine where you are running the code.

           

          I had no problems with the appending either, but I just used the file that had been created already via my automated export, rather than making one by hand.

           

          One thing to check is that the Excel formatting is checked for the particular model, in case you were checking the setting without having a model loaded.

           

          Originally posted by John Fitzgerald:

          I'm learning to use the OLE programming methods with Monarch (v6 Pro) and having a few minor issues using the JetExportTable() method.  First, when the data was sent to Excel, the formatting of the fields (i.e. 2 decimal places, commas between Thousands) was not preserved.  In Monarch under "Folders and File Types" I have selected Excel 8.0, and under "Export and Print Options" I've got the "Apply formatting to XLS files" checked - which I thought would be the settings used by the method.

           

          Using MonarchObj.JetExportTable("CompanyName.xls", "July22", 1)[/b] created the worksheet in the Excel file, but without formatting.

           

          Trying to get around that, I created a table in Excel called "July22", formatted the cells, and then tried to do it as an append using  MonarchObj.JetExportTable("CompanyName.xls", "July22", 2)[/b] .  This time, it created a new table called "July221", again without the applied formatting.  Just for kicks, I tried running the macro again, and it DID append the data to the "July221" table.

           

          Could someone tell me if it is possible to preserve formatting of data into Excel using JetExportTable, and also what I can do to properly append data?

           

          Thanks![/b][/quote]

          • Formats and Appending Using JetExportTable to Excel
            John Fitzgerald

            Finally got around to playing with this again.  I double checked to make sure the "Apply formatting to XLS files" box was checked, both with the model open and without.  Still, the formatting isn't going through.  I do have Excel running on the same machine.

             

            One thing I noticed - the "Apply formatting to XLS files" checkbox seems not to apply to the model, just the Monarch program itself.  I changed settings with the model open, and when I closed the model it didn't ask if I wanted to save changes.  I opened it again, changed the setting, hit "Update", and saved anyway, but still had the same result.  When I run it manually from the model file it applies the formatting, but using JetExportTable, it doesn't.  Any suggestions?  Where is the setting in the .mod file?  Not that I'd EVER hack the model file...

             

            I'll keep trying to figure out where a different setting might be coming from, but I'd really like some suggestions.  Automating this would be a HUGE time saver.

             

            Thanks,

            -JF