2 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Macro to open csv files and export as text file.

    MonarchUser _

      Hello,

       

      I am trying to modify the code to open 20 csv files saved in a folder as a database, apply model and then export them as text files individually. The code is working fine till opening all the csv files. I am not sure how to loop them to name the export correctly. For example if the csv file is PD1234, I want the exported text file to be named PD1234.txt and so on for each of the 20 files. The files will change everymonth. I am using MonarchPro. Any help will be appreciated.

       

      Public Sub PoolDefFiles()

       

      Dim s As String

      Call Location

       

          AnyPoolDef = Path

          SearchString = "*.csv"

       

      End Sub

       

      Public Sub MonarchPoolDef()

       

      Dim objMonarch As Object

      Dim fPoolDef As Boolean

      Dim fPoolDefAll As Boolean

      Dim bReportStatus As Boolean

      Dim s As String

      Dim bOpenType As Boolean

       

       

       

      'References the Public Funtion Location() & PoolDefFiles() for use in this funtion.

      Call Location

      Call PoolDefFiles

       

      'Create Monarch Object

        Set objMonarch = CreateObject("Monarch32")

        Debug.Print Path

        Debug.Print CurDir

         

        ' Opens all reports.

        s = Dir(AnyPoolDef & SearchString, vbNormal)

        fPoolDef = True

        bOpenType = False

         

         

          Do While s <> ""

                   bReportStatus = objMonarch.OpenDatabase(AnyPoolDef & s, "", s, Path & "models\csv.xmod")

                   bOpenType = True

                    

                   If bReportStatus = False Then Debug.Print "Failed to open ->" & AnyPoolDef & s

                   fPoolDef = fPoolDef And bReportStatus

                   s = Dir()

                    

                   If objMonarch.SetModelFile(Path & "models\csv.xmod") Then

              Call objMonarch.ExportTable(Path & "exports\abc.txt")

            End If

             

          Loop

        • Macro to open csv files and export as text file.
          Data Kruncher

          I think that you'll be OK if you change your Do Loop as follows:

              Do While s <> ""

                  bReportStatus = objMonarch.OpenDatabase(AnyPoolDef & s, "", s, Path & "models\csv.xmod")

                  bOpenType = True

           

                  If bReportStatus = False Then Debug.Print "Failed to open ->" & AnyPoolDef & s

                  fPoolDef = fPoolDef And bReportStatus

           

                  If fPoolDef And objMonarch.SetModelFile(Path & "models\csv.xmod") Then

                      Call objMonarch.ExportTable(Path & "exports\" & Left(s, Len(s) - 4) & ".txt")

                  End If

                  s = Dir()

              Loop

          /code