5 Replies Latest reply: May 15, 2014 9:58 AM by Brian Hammer RSS

    Export Multiple Files

    Ron Raes

      A Monarch 6 pdf file stated that from the summary, one could export multiple excel files or one excel file with multiple worksheets. I was able to export one excel file with multiple worksheets but I can't figure out how to export multiple files, one for each summary. Do you know how this works?

        • Export Multiple Files
          Tom Whiteside

          Hi, Ron!

           

          I think you are looking at the same .pdf file that I am looking at, the Monarch V6 Upgrade Brochure, topic 4, on page 3.  What it actually says, is, "... Monarch V6 now lets you export all of your summary views at the same time, either as separate files in a desired folder, or as multiple spreadsheet pages or databases within a single file!"

           

          Unfortunately, the options do not include separate Excel spreadsheet files in a desired folder.  What actually happens is that your choice of export file format for the summaries, i.e., the file extension, determines whether you may export as multiple files within a folder, or, as multiple sheets/tables within a file.

           

          The file extensions that will produce multiple sheets/tables within one file are: mdb, xls, and wk3.  All the other listed file extensions, db, dbf, htm, txt, wk1, wks, and xml, will produce multiple files within a folder.  Note that the xml option is labeled "Excel 2002 XML Spreadsheet."  As I have not yet been able to successfully setup the Office XP Web Components, I cannot tell you how the xml option looks as output.

           

          Looking through the Monarch Knowledge Base and Archive, several postings were made before Monarch V6 came out, that used VBA code to export a sequence of summaries to either a single Excel file, or to multiple Excel files.

           

          Could either Nick Osdale-Popa, or Gareth help out here?  They were the two most frequent code contributors.

           

          Anyway, sorry not to have a more cheerful answer.  Other people have misread that same feature/benefit point.  Hopefully, you can get some good code out of this!

           

          Best wishes!

           

          Tom Whiteside

          whitesidetom@yahoo.com[/email]

          • Export Multiple Files
            Stephen Reid

            Hi, try the following code.  You will need to change the txt and mod files to your own and also the export directory.

             

            Sub Export_Routing()

                 

                Dim Monarchobj As Object  ' Define the Object

                Dim openfile As Boolean   ' True/False variable used for opening report

                Dim SummaryCount As Integer ' Holds the # of Summaries in the model

                Dim LoopCounter As Integer            ' Variable used in For loop

                 

                Set Monarchobj = CreateObject("Monarch32") 'Create the object

                 

                 

                openfile = Monarchobj.setreportfile("J:/cs.txt", False) 'Open the report file

                If openfile = False Then

                    MsgBox ("Unable to open Report!")

                    End

                End If

                 

                openfile = Monarchobj.setmodelfile("S:steve_rmonarchModels outing.mod")  ' Open the model file

                If openfile = True Then

                    SummaryCount = Monarchobj.SummaryCount                 ' Determine the # of summaries

                    For LoopCounter = 0 To (SummaryCount - 1) Step 1

                        Monarchobj.CurrentSummary = Monarchobj.GetSummaryNameAt(LoopCounter)         'Apply the Summary

                        Monarchobj.exportsummary ("S:steve_rTemp oute" & Monarchobj.GetSummaryNameAt(LoopCounter) & ".XLS")   'Export the Summary

                    Next LoopCounter

                Else

                    MsgBox ("Unable to open Model File")

                    End

                End If

                Monarchobj.closealldocuments

                Monarchobj.Exit

                Set Monarchobj = Nothing

                 

                 

            End Sub

             

            Hope this helps.  Sorry the code isnt laid out correctly but I couldn't get tabs to work.

            • Export Multiple Files
              Nick Osdale-Popa

              That looks like it should accomplish what Ron needs.  Stephen, if you start and end your code snippets with [/code] you'll get better formatting of your code. (blank lines seem to get dropped, though  )

              • Export Multiple Files
                Stephen Reid

                Just re-posting my code with Nick's tip.

                 

                Thanks for that Nick, I appreciate it.

                  smile.gif[/img] 

                 

                [font="courier"]Sub Export_Routing()

                     

                    Dim Monarchobj As Object  ' Define the Object

                    Dim openfile As Boolean   ' True/False variable used for opening report

                    Dim SummaryCount As Integer ' Holds the # of Summaries in the model

                    Dim LoopCounter As Integer            ' Variable used in For loop

                     

                    Set Monarchobj = CreateObject("Monarch32") 'Create the object

                         

                    openfile = Monarchobj.setreportfile("J:/cs.txt", False) 'Open the report file

                    If openfile = False Then

                        MsgBox ("Unable to open Report!")

                        End

                    End If

                     

                    openfile = Monarchobj.setmodelfile("S:steve_rmonarchModels outing.mod")  ' Open the model file

                    If openfile = True Then

                        SummaryCount = Monarchobj.SummaryCount                 ' Determine the # of summaries

                        For LoopCounter = 0 To (SummaryCount - 1) Step 1

                            Monarchobj.CurrentSummary = Monarchobj.GetSummaryNameAt(LoopCounter)         'Apply the Summary

                            Monarchobj.exportsummary ("S:steve_rTemp oute" & Monarchobj.GetSummaryNameAt(LoopCounter) & ".XLS")   'Export the Summary

                        Next LoopCounter

                    Else

                        MsgBox ("Unable to open Model File")

                        End

                    End If

                    Monarchobj.closealldocuments

                    Monarchobj.Exit

                    Set Monarchobj = Nothing

                     

                End Sub[/font][/quote]

                • Export Multiple Files
                  Brian Hammer

                  Hi guys!

                   

                  That code tip is great, but I already knew how to do that one.  My question is how do I go about putting the summaries into the same workbook, but separate worksheets?

                   

                  I have version 4, so I cannot use the JetExportSummary utility.  What I want to do is export 3 summaries into 3 separate worksheets in an Excel file.  I know how to do it by using VBA code within an Excel Macro and importing the data into the current Excel file, but I'd like to do it from VB.  I'd like a VB app that will use Monarch's abilities to create one Excel file containing these 3 worksheets.

                   

                  Is this possible?  If not I can go ahead and put my code into an Excel Macro and do it that way, but I really don't want to, if I don't have to.  Thanks for any help you guys can provide!