2 Replies Latest reply: May 15, 2014 9:55 AM by RalphB _ RSS

    Example of VBA to process multiple reports

    Mark Huston

      List member nahla was asking for an example of VBA code for looping through multiple reports.


      Note: By far the simplest way to deal with multiple reports is to dump them all in one folder, then open them all in Monarch, apply the model, and the resulting output includes the data from all the reports. More on that  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000472"]here.[/url]

      Unfortunately, our server distributes daily reports into a bunch of different folders, so I use this code to accumulate them. I've removed the code specific to our network structure. Watch out for word wrap.



      '<< variables dimmed here >>

      Sub GO_Monarch()

      '(Set up input and output folders:)

      PathName = "C:MyReportFolder" '(Acts on all files in this folder)

      ModPath = "S:SharedMonarchModelsMyModels"

      ModFile = ModPath & "MyModel.mod"

      DestPath = "C:MyCompletedReports"

      DestFile = DestPath & "MyReport_" & LoopCounter & ".xls"


      LoopCounter = 1

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

      If MonarchObj Is Nothing Then

          Set MonarchObj = CreateObject("Monarch32")

      End If

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


      fName = Dir(PathName & ".")  '(Set up dir and returns first file in Pathname)

      While (fName <> "")  '(Loops until "" found)

          If (fName <> ".") And (fName <> "..") Then

              '(Skip files in folder that are different from the ones you want to process)

              If Left(fName, 12) = "MyReportName" Then

                  '(Optional - Omit file size greater than 4000 kB)

                  fSize = Format(FileLen(PathName & fName) * 0.0009767, "0")

                  If fSize < 4000 Then

                      PathAndFile = PathName & fName

                      OpenFile = MonarchObj.SetReportFile(PathAndFile, False)

                      If OpenFile = True Then

                          OpenMod = MonarchObj.SetModelFile(ModFile)

                          If OpenMod = True Then

                              With MonarchObj

                                  '(Optional - apply filter here)

                                  .CurrentFilter = "MyFilter"

                                  '(You could use ExportTable(DestFile) here instead)

                                  .ExportSummary (DestFile)

                              End With

                              LoopCounter = LoopCounter + 1

                          Else: MsgBox "Model file not found."

                              Exit Sub

                          End If '(OpenMod = True)

                      End If '(OpenFile = True)

                  End If '(fSize < 4000)

              End If 'Left(fName, 12) = "MyReportName"

          End If '((fName <> ".") And (fName <> ".."))

      fName = Dir()





      'Open all the output workbooks.

      'You need to have set up a target workbook where the daily data will be accumulated.


      PathName = "C:MyCompletedReports" '(Acts on all files in this folder)

      fName = Dir(PathName & ".")  '(Set up dir and returns first file in Pathname)


      While (fName <> "")  '(Loops until "" found)

          If (fName <> ".") And (fName <> "..") Then

              If Left(fName, 8) = "MyReport" Then

                  Workbooks.Open (PathName & fName)

              End If

          End If

      fName = Dir()


      Workbooks.Open (PathName & "TargetWorkbook.xls")



      'Copy all open data sheets to TargetWorkbook.xls workbook

      Application.DisplayAlerts = False


      For Each wb In Workbooks

          fName = Left(wb.Name, 8)

          If fName = "MyReport" Then


              If = "" Then '(Report's empty, skip it)


                  Range(, .End(xlUp).Offset(-1, 0).End(xlToRight)).Copy

                  '(The offset method omits the "Summary" line.)


                  .End(xlUp).Offset(1, 0).Activate


              End If

          End If

      Next wb

      'Close 'em down.

      For Each wb In Workbooks

          fName = wb.Name

          If Left(wb.Name, 8) = "MyReport" Then


              Kill (PathName & fName) '(Delete the Monarch output files)

          End If

      Next wb

      End Sub



      [size="1"][ May 18, 2006, 11:27 AM: Message edited by: Todd Niemi ][/size]