6 Replies Latest reply: May 15, 2014 10:03 AM by sujeet _ RSS

    Macro to convert txt files into excel

    sujeet _

      Hi All,

       

      I have written a small macro, wherein I have 100's of files in one folder which are converted in excel and saved at another folder.While doing so I am not able to maintain the "FILENAME" of the text file to "Excel File". Not sure if I am using the functionality right. :mad:

      Below is the macro.

      ===================

      Option Explicit

       

      Sub convert2excel()

      Dim Myapplication As Object

      Dim openfile, openmodel, T As Boolean

       

      Dim myWin As String

      Dim myexport As String

      Dim i As Integer

      Dim mysearch As Object

      Dim myfolder As String

      Dim MyModelFldr As String

      Dim appClose As Boolean

      Dim myexportfinal1 As Boolean

      Dim prefix As Integer

       

       

       

      'Ask user to give drive paths to respective folders

      myfolder = InputBox("Please specify the folder", "Folder Path", "D:\")

      MyModelFldr = InputBox("Please specify the model", "Models", "D:\")

      myexport = InputBox("Please specify folder name to export files", "Export Folder")

       

       

      Application.ScreenUpdating = False

       

      'Start Monarch session

       

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

      If Myapplication Is Nothing Then

          Set Myapplication = CreateObject("Monarch32")

      End If

       

      'Search the count of files and convert each one of it into excel

      Set mysearch = Application.FileSearch

      With mysearch

      .NewSearch

      .LookIn = myfolder

      .SearchSubFolders = False

      .Filename = "*.txt"

      .Execute

      For i = 1 To .FoundFiles.Count

       

       

      'Open txt file

      openfile = Myapplication.SetReportFile(mysearch.FoundFiles(i), False)

       

       

      'Open model

      openmodel = Myapplication.SetModelFile(MyModelFldr)

       

      'Convert data into Table view

      myWin = Myapplication.SetView("t")

      prefix = prefix + 1

       

      'Export data to excel file on the specified path

      myexportfinal1 = Myapplication.exporttable(prefix & ".xls")

       

      'Close all the open txt file and model

      appClose = Myapplication.CloseAllDocuments()

       

      'Loop the process

      Next i

       

      Application.ScreenUpdating = True

       

      End With

      End Sub

      ================

       

      I appreciate if anyone can guide me and correct the macro above.:)

        • Macro to convert txt files into excel
          Data Kruncher

          Hi Sujeet. Welcome to the forum.

           

          Filesearch is a useful approach, but a simpler solution - and for your needs, better as well - exists in using Dir in a loop.

           

          Sub convert2excel()

              Dim Myapplication As Object

              Dim mysearch As Object

           

              Dim openfile As Boolean

              Dim openmodel As Boolean

              Dim appClose As Boolean

              Dim myexportfinal1 As Boolean

              Dim T As Boolean

           

              Dim myWin As String

              Dim myexport As String

              Dim myfolder As String

              Dim MyModelFldr As String

              Dim rptFile As String

           

              Dim i As Integer

              Dim prefix As Integer

           

              'Ask user to give drive paths to respective folders

              myfolder = InputBox("Please specify the folder", "Folder Path", "D:\")

              MyModelFldr = InputBox("Please specify the model", "Models", "D:\")

              myexport = InputBox("Please specify folder name to export files", "Export Folder")

           

              Application.ScreenUpdating = False

           

              'Start Monarch session

           

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

              If Myapplication Is Nothing Then

                  Set Myapplication = CreateObject("Monarch32")

              End If

           

              rptFile = Dir(myfolder & "*.txt")

              Do While rptFile <> ""

                  If rptFile <> "." And rptFile <> ".." Then

                  'assumes no subdirectories in myfolder

           

                      'Open txt file

                      openfile = Myapplication.SetReportFile(rptFile, False)

           

                      'Open model

                      openmodel = Myapplication.SetModelFile(MyModelFldr)

           

                      'Convert data into Table view

                      myWin = Myapplication.SetView("T")

           

                      'Export data to excel file on the specified path

                      myexportfinal1 = Myapplication.exporttable(myexport & Left(rptFile, Len(rptFile) - 4) & ".xls")

           

                      'Close all the open txt file and model

                      appClose = Myapplication.CloseAllDocuments()

                  End If

           

                  'Loop the process

                  rptFile = Dir

              Loop

           

              Application.ScreenUpdating = True

               

          End Sub[/code]You may want to consider using jetexporttable instead of exporttable if you have the Pro edition of Monarch.

           

          Once you have the filename in rptFile, you can strip the .txt extension and add the .xls extension for the export file.

           

          HTH,

          Kruncher

            • Macro to convert txt files into excel
              sujeet _

              Thanks for your warm welcome in the forum.

               

              I tried to use the below code and it is actually working very good.Thanks for guiding me in this.

               

              Best Regards,

              Sujeet

                • Macro to convert txt files into excel
                  Data Kruncher

                  I've revised the code a little as I'd missed an "End If" and had an extra "End With" that wasn't necessary.

                    • Macro to convert txt files into excel
                      sujeet _

                      Hi,

                       

                      Yup I later changed it.Also I did not give the path with " \"  ....hence it was not picking up the files also. Thanks again for your kind help.

                        • Macro to convert txt files into excel
                          Nick Osdale-Popa

                          I, myself, have had inconsistent results using Filesearch. In my macros I have it using a similar routine that Kruncher has used when FileSearch fails.

                           

                          Also, in your routine, you have the macro closing all documents. Prior to the loop, you can set your model and do the closealldocuments after the loop. Thus you eliminate opening/reopening the model.

                           

                          Also, Kruncher has changed your variables appropriately.

                          With your line:

                          Dim openfile, openmodel, T As Boolean

                           

                          Only T will be boolean, whereas openfile and openmodel will be variants. No big deal with today's computers, but there is some overhead when the macro has to convert the variables to the appropriate type.

                           

                          Hope that helps!

                            • Macro to convert txt files into excel
                              sujeet _

                              Hi All,

                               

                              I am coming back with the same macro. Well I have created a user form, for users to specify path for text files and to save converted file. Now the challenge is when a user specifies path in the textbox (as per below macro) and the path already existing in Monarch's folder then it does not work.

                              I need my macro to accept path's which are provided on userform and not consider paths in monarch's path folder. Please help.

                               

                              Macro :----


                              >

                               

                              Private Sub cmdConvertXL_Click()

                              Dim Myapplication As Object

                                  Dim mysearch As Object

                               

                                  Dim openfile As Boolean

                                  Dim openmodel As Boolean

                                  Dim appClose As Boolean

                                  Dim myexportfinal1 As Boolean

                                  Dim T As Boolean

                               

                                  Dim myWin As String

                                  Dim myexport As String

                                  Dim myfolder As String

                                  Dim MyModelFldr As String

                                  Dim rptFile As String

                               

                                  Dim i As Integer

                                  Dim prefix As Integer

                               

                                  'Ask user to give drive paths to respective folders

                                  myfolder = txtTextFiles.Text

                                  MyModelFldr = txtModelFolder.Text

                                  myexport = txtExcelFolder.Text

                                 

                                   

                                   

                                  If txtTextFiles.Text = "" Or txtModelFolder.Text = "" Or txtExcelFolder.Text = "" Then

                                  MsgBox "Please update all the paths correctly", vbOKOnly, "Path Error"

                                  txtTextFiles.SetFocus

                                  Exit Sub

                                  End If

                                   

                                   

                                   'Start Monarch session

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

                                      If Myapplication Is Nothing Then

                                  Set Myapplication = CreateObject("Monarch32")

                                  End If

                                   

                                   

                                   

                                    

                                  Application.ScreenUpdating = False

                               

                               

                               

                                  rptFile = Dir(myfolder & "*.txt")

                                  Do While rptFile <> ""

                                      If rptFile <> "." And rptFile <> ".." Then

                                      'assumes no subdirectories in myfolder

                                    

                                  

                                          'Open txt file

                                          openfile = Myapplication.SetReportFile(rptFile, False)

                               

                                          'Open model

                                          openmodel = Myapplication.SetModelFile(MyModelFldr)

                               

                                          'Convert data into Table view

                                          myWin = Myapplication.SetView("T")

                               

                                          'Export data to excel file on the specified path

                                          myexportfinal1 = Myapplication.exporttable(myexport & Left(rptFile, Len(rptFile) - 4) & ".xls")

                               

                                          'Close all the open txt file and model

                                          appClose = Myapplication.CloseAllDocuments()

                                  End If

                                          'Loop the process

                                          rptFile = Dir

                                  Loop

                               

                                  Application.ScreenUpdating = True

                               

                               

                              End Sub