3 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    CSV limit when exporting with VBA

    BaWahoo _

      I have a VBA code that will bring in a text file, then export the file into CSV.  However, the CSV file is limited to 65K rows and my text file has more then 65K rows. Is there a work around this? Thanks in advance.

       

      CODE SAMPLE:

       

      Set MonarchObj = CreateObject("Monarch32")

          With MonarchObj

              openfile = .setreportfile("D:\Credit Bal Rpt\PXCAI2_Rpt.txt", False)

              If openfile = True Then

              openmod = .setmodelfile("D:\Credit Bal Rpt\PXCAIx.xmod")

       

                  If openmod = True Then

                      .ExportTable ("D:\Credit Bal Rpt\PXCAI2.csv")

                  End If

              End If

              Workbooks.Open Filename:="D:\Credit Bal Rpt\PXCAI2.csv"

              Windows("PXCAI2.csv").Activate

              ActiveWorkbook.SaveAs Filename:="R:\Credit Balance Rpt\PXCAI2_" & Date$ & ".csv", FileFormat:=xlCSV, _

              CreateBackup:=False

              Application.DisplayAlerts = False

              ActiveWindow.Close

       

          .CloseAllDocuments

          .Exit

        • CSV limit when exporting with VBA
          Data Kruncher

          You may get better results in creating the full csv file by using .JetExportTable instead of using .ExportTable. See the programmers manual for the JetExportTable syntax.

           

          I don't usually export to text files, so I'm not really certain if there are limitations there, off the top of my head.

           

          But if you're using XL 2003 or earlier, the size of the exported csv file (assuming Monarch exports all of your text file) is your big problem, not how you ask Monarch to create it.

           

          The minute you import the fresh csv file into Excel, you'll run into Excel's row limit.

           

          Since you're not actually making any changes to the data file itself, you're really only renaming it, use FileCopy instead:

           

          FileCopy "D:\Credit Bal Rpt\PXCAI2.csv","R:\Credit Balance Rpt\PXCAI2_" & Date$ & ".csv"[/code]

           

          ought to do it for you.

           

          Kruncher