4 Replies Latest reply: May 15, 2014 9:54 AM by JoeB _ RSS

    Opening a report file

    JoeB _

      I am trying to build a model that is easy for not technical personnel to use.  I have a project file that can open a spool file from the user’s windows print queue and export a summary table to an Excel file using the following code.

       

      Sub GL1stQtr()

      Dim MonarchObj As Object

      Dim openfile, t, exportfile As Boolean

              Set MonarchObj = GetObject("", "Monarch32")

                  If MonarchObj Is Nothing Then

                      Set MonarchObj = CreateObject("Monarch32")

                  End If

          t = MonarchObj.SetLogFile("C:data     estMPrg_G5.log", False)

          openfile = MonarchObj.SetProjectFile(ThisWorkbook.Path & "GLPayroll.prj")

          exportfile = MonarchObj.JetExportSummary(ThisWorkbook.Path & "Data.xls", "GLData", 0)

          MonarchObj.CloseAllDocuments

          MonarchObj.Exit

      End Sub

       

       

      The problem that I am having is that GLPayroll.prj is looking for a specific file in the users print queue (i.e. 00016.SPL).  I do not know what name windows will assign to the spool file when the user runs the report from our AS400.  I know that it will have a SPL extension but the name will be some number like 00017.SPL or 00023.SPL.  Is there a way that I can tell Monarch from excel to take the first SPL file that it finds in the windows print queue and use that file? 

       

      Currently I get this error message in the MPrg_G5.log file when I run the sub GL1stQtr from an Excel file.

       

      “Cannot open file C:WINDOWSsystem32spoolPRINTERS

        • Opening a report file
          Nick Osdale-Popa

          assign a variable to Dir("*.SPL")

          and then pass that variable as the filename to Monarch.

          • Opening a report file
            Winn _

            Nick,

             

                The problem with that is that the project file is set up to look at the same input filename every time. So a variable won't change that.

             

            Joe,

             

                I have to run a report at the end of the month which extracts data from every day during the previous month. I have a procedure that runs through the files one at a time, copies the file to a fixed file and then opens the Monarch project file.

             

            You can do this by setting up an integer variable to cycle through your files and use the FileCopy statement to copy your file to a fixed filename. For example, if you were looking for the file with the highest number in the name, you might try something like this.

             

            [font="courier"]Sub test()

                Dim FileNum as integer

                'put your other variables here

                On Error Goto NextFile

                FileNum = 99

                FileCopy SpoolPath&" 0"&CStr(FileNum)&".spl","DestFile"

                'Put your Monarch code here.

                Exit Sub

            NextFile:

                if Err = 53 then

                    FileNum=FileNum-1

                    Resume

                end if

            end sub[/font][/quote]I don't guarantee that this will work for you as written, but it should give you some idea about how to write your own procedure.

             

            Hope this helps.

             

            [size="1"][ April 08, 2003, 11:45 AM: Message edited by: Winn ][/size]

            • Opening a report file
              Nick Osdale-Popa

              Winn, good point... I don't work with .PRJ files... as you pointed out a rename routine should work fine.

              • Opening a report file
                JoeB _

                Win,

                 

                I like your solution and will give it a try. 

                 

                Thanks for the help.

                 

                Joe