CODEWITHSUNDEEP
excelexport-to-excelprogress-4glopenedge
Takumi
Takumi

Reputation: 3

Progress 4GL - Export large amount of data to Excel

Hi there fellow developers,

I'm trying to export a large amount of data to Excel using Progress 4GL. We're talking about ±5500 records and ±170 columns. The problem is that this is too much for Excel to handle and it raises the following error: Excel error. I can continue and it exports all the data, but the procedure isn't exactly presentable with an error right in the middle of it.

I'm using the following code to export all data from different CSV files into one single Excel file with a seperate worksheet for each CSV file:

  /* Initialize Excel file & add new workbook */
  CREATE "Excel.Application" vchExcel.
  vchExcel:SheetsInNewWorkbook = NUM-ENTRIES(ipcSheets,',').
  vchExcel:WorkBooks:ADD().
  ASSIGN vchWorkBook    = vchExcel:WorkBooks:Item(1).

  /* Import each file's data into a new sheet of the workbook */
  Sheet:
  DO iTab = 1 TO NUM-ENTRIES(ipcFiles) ON ERROR UNDO, RETRY Sheet:
      /* Import CSV data into Excel */
      ASSIGN cConnection        = SUBSTITUTE("TEXT;" + gvcExportPath + "csv_files\" + "&1",ENTRY(iTab,ipcFiles))
             vchWorkSheet       = vchExcel:Sheets:ITEM(iTab)
             vchWorkSheet:NAME  = ENTRY(iTab,ipcSheets)
             lResult            = vchWorkSheet:QueryTables:ADD(cConnection,vchWorkSheet:cells(1,1)).

      ASSIGN
          vchQueryTable = vchWorkSheet:QueryTables(1)
          vchQueryTable:FieldNames = TRUE
          vchQueryTable:RowNumbers = FALSE
          vchQueryTable:FillAdjacentFormulas = FALSE
          vchQueryTable:PreserveFormatting = FALSE
          vchQueryTable:RefreshOnFileOpen = FALSE
          vchQueryTable:RefreshStyle = 1
          vchQueryTable:SavePassword = FALSE
          vchQueryTable:SaveData = TRUE
          vchQueryTable:AdjustColumnWidth = TRUE
          vchQueryTable:RefreshPeriod = 0
          vchQueryTable:TextFilePromptOnRefresh = FALSE
          vchQueryTable:TextFilePlatform = 437
          vchQueryTable:TextFileStartRow = 1
          vchQueryTable:TextFileParseType = 1
          vchQueryTable:TextFileTextQualifier = 1
          vchQueryTable:TextFileConsecutiveDelimiter = TRUE
          vchQueryTable:TextFileTabDelimiter = TRUE
          vchQueryTable:TextFileSemicolonDelimiter = TRUE
          vchQueryTable:TextFileCommaDelimiter = FALSE
          vchQueryTable:TextFileSpaceDelimiter = FALSE
          vchQueryTable:TextFileTrailingMinusNumbers = TRUE
          lResult = vchQueryTable:REFRESH
          vchQueryTable:BackgroundQuery = FALSE.


      /* Catch all errors */
      CATCH eAnyError AS Progress.Lang.ERROR:
          RUN disp_mesg(INPUT SUBSTITUTE("ERROR! -> &1",eAnyError:GetMessage(1))).
          RUN adnew_log(INPUT SUBSTITUTE("ERROR OCCURRED!")).
          RUN adnew_log(INPUT SUBSTITUTE("=> &1: &2",eAnyError:GetMessageNum(1),eAnyError:GetMessage(1))).
      END CATCH.
  END.

  /* Save & close */
  vchExcel:Visible = FALSE.
  vchExcel:DisplayAlerts = FALSE.
  vchWorkBook:SaveAs(gvcExportPath + ipcName + '.xlsx',,,,,,).
  vchWorkBook:CLOSE().

  /* Release All Objects */
  RELEASE OBJECT vchQueryTable NO-ERROR.
  RELEASE OBJECT vchWorkSheet  NO-ERROR.
  RELEASE OBJECT vchWorkBook   NO-ERROR.
  vchExcel:QUIT(). /* Quit Excel */
  RELEASE OBJECT vchExcel NO-ERROR.

Does any of you know how to solve this issue and get the desired result? Thanks in advance!

Upvotes: 0

Views: 1678

Answers (1)

Romuald
Romuald

Reputation: 1

Why controlling XL from Progress?

If XL is a mandatory step... I would have rather do it the way around... call XL with a macro-file to open the CSVs and save in XL format. If this needs to be integrated into a business process... I would just open XL with the macro-file right after the CSVs generation.

Upvotes: 0

Related Questions