7 Replies Latest reply: May 15, 2014 9:55 AM by Nick Osdale-Popa RSS

    Using Command Lines to Export to Excel

    gpotenza _

      I've read the information on using command lines to export tables from a project file to as Excel or other formats but I can't seem to figure out how to get started.  Can someone give me an idea of how to do this?  Some basic questions, I need answered:

       

      1.  How and where do I create the Command Lines

      2.  Ultimately I would like to append the data to an existing table in MS Access (currently using Monarch v6.  Is this even possible in this version?

      3.  I have multiple project files.  Is it possible to create one batch file to run through all project files, one after the other?

       

      Thanks, I'm just not sure how to get started here.

        • Using Command Lines to Export to Excel
          Bob Yarnall

          I don’t have experience with parts 2 and 3 of your question, but this should get you started with part 1.

          This is a VB script I use occasionally.

           

          [font="courier"] 

          Set MonarchObj = CreateObject("Monarch32")

          openfile = MonarchObj.SetLogFile("c:monarch eportsVBS.LOG",False)

           

          openfile = MonarchObj.SetProjectFile("c:monarchPublishContractEmployees.prj")

          If openfile Then

             MonarchObj.ExportTable("c:MonarchExportContract.wks")

             end if

           

          Monarchobj.closealldocuments

          Monarchobj.Exit

          Set Monarchobj = Nothing[/font][/quote]

          • Using Command Lines to Export to Excel
            gpotenza _

            Please excuse my ignorance, but where do I put this code? In a bat file?

            • Using Command Lines to Export to Excel
              gpotenza _

              ok, I've been able to get this to work just as I needed.  Is there a way to specify the version of excel this exports to?  When I am testing with an import to Access, the version is not recognized.

              • Using Command Lines to Export to Excel
                Nick Osdale-Popa

                In Monarch, select Options|Export, set the XLS Files to the version you need.

                • Using Command Lines to Export to Excel
                  Phil _

                  Originally posted by gpotenza:

                  ok, I've been able to get this to work just as I needed.  Is there a way to specify the version of excel this exports to?  When I am testing with an import to Access, the version is not recognized. /b[/quote]How did you get this to work please?

                  • Using Command Lines to Export to Excel
                    Mark Huston

                    To answer your first question, there are two ways to use command lines. The first way is to use DOS batch files. There have been some examples posted on this forum. The second way is to use VB code, which can be run through Excel.

                     

                    Take a look at an example  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000517"]here[/url].

                     

                    Within Excel, select Tools > Macro > Visual Basic Editor. Bob's code would need to be in a named sub, such as Sub ExportReports(), followed by an End Sub. Once you've added a subroutine to a code module, the name of that macro is available to Excel under Tools > Macro > Macros.

                     

                    To answer your third question, yes, it's possible to loop through any number of reports and export the data. The choices of ways to do that depend on your version. Are you using version 6 of Monarch or Monarch Pro?

                     

                    [size="1"][ May 18, 2006, 05:14 PM: Message edited by: Todd Niemi ][/size]

                    • Using Command Lines to Export to Excel
                      Nick Osdale-Popa

                      A third option is Visual Basic Scripting, which Bob pointed out.

                       

                      Cut/Paste the code Bob provided into notepad. Change the report name and path to your report and the model name and path to your model. Save the file with a .VBS extension. You can now double-click on that file and it will run monarch and export your report.