Exception from HRESULT: 0x800A01A8 Dynamic Navision 2016

Question

I wrote a code where user filters a source number from the table, the code get the source number and open an Excel file with the same number in a folder, write other data into the file and then save to temp folder. The program worked for some of the Excel files in the folder, but it won't work for the majority of the files. When it finishes running it'll say Exception from HRESULT: 0x800A01A8, the file gets saved into temp folder but the data is not written into it.

https://i.stack.imgur.com/kHtbC.png

I have searched all over Google but it seems like no one had encountered the same issue as I am and I have no idea what this exception means. Below is the code.

ProductionOrder - OnAfterGetRecord()

  CLEAR(xlApplication);
  CLEAR(xlWorkbooks);
  CLEAR(xlWorksheet);
  CLEAR(xlshape);

  // Open excel
  IF CREATE(xlApplication, FALSE, TRUE) THEN BEGIN
    xlApplication.SheetsInNewWorkbook := 1;
    xlApplication.ScreenUpdating(TRUE);
    xlWorkbooks := xlApplication.Workbooks;
  END ELSE ERROR('Could not start Excel.');

  xlWorkbooks.Open('C:\PROCESS CHECKSHEET\' + ProductionOrder."Source No." + '.xlsx');
  xlWorkbook := xlApplication.ActiveWorkbook;
  xlSheets := xlWorkbook.Worksheets;

  FOR i := 1 TO xlSheets.Count DO BEGIN
    xlWorksheet := xlSheets.Item(i);
    xlWorksheet.Activate;

    xlRange := xlWorksheet.Range(xlsCell(14,7));
    xlRange.Value := ProductionOrder."No.";

    xlRange := xlWorksheet.Range(xlsCell(14,8));
    xlRange.Value := FORMAT(ProductionOrder.Quantity);

    xlWorkbook._SaveAs('C:\temp\' + ProductionOrder."Source No.");
    xlWorkbook.Close(TRUE);
    xlApplication.Quit;
  END;

  CurrReport.QUIT;

LOCAL xlsCol(col : Integer) : Text

    IF col > 26 THEN BEGIN
      ColFirst := col DIV 26;
      col := col MOD 26;
    END
    ELSE
      ColFirst := 0;
      Letters := 'ABCDEFGHIJKLMNOPQRSTUVYWXYZ';
    IF ColFirst <> 0 THEN
      EXIT (STRSUBSTNO('%1%2',Letters[ColFirst],Letters[col]))
    ELSE
      EXIT (STRSUBSTNO('%1',Letters[col]));

LOCAL xlsCell(col : Integer;row : Integer) : Text[15]

  EXIT (STRSUBSTNO('%1%2',xlsCol(col),row));

Edit:

I have tried to debug. Debugger says error is on line "xlWorksheet := xlSheets.Item(i);". There is only one sheet in the Excel file that I am trying to access. What I don't understand is that it would work on other Excel files, just not on this file that I am currently trying to access.

I also found out that if I copy the content into a new Excel file, then the code would work on the new Excel file. Could it also be a problem of Excel version?

Answer 1

I solved this on my own.

Because the Excel files were given to me by my HOD at work (and these Excel files were written by other department), I did not know that there was a hidden sheet inside those Excel files, hence it output the exception error because my code was only indexing Excel file that only has one sheet. I changed my index to 2 and it worked. Note to self, next time gotta check for hidden sheets.

I don't know if there is a for each sheet equivalent for C/AL programming because I'm new to the language, so I used 2 for loops to solve the problem.

  CLEAR(xlApplication);
  CLEAR(xlWorkbooks);
  CLEAR(xlWorksheet);
  
  IF CREATE(xlApplication, FALSE, TRUE) THEN BEGIN
    xlApplication.SheetsInNewWorkbook := 1;
    xlApplication.ScreenUpdating(TRUE);
    xlWorkbooks := xlApplication.Workbooks;
  END ELSE ERROR('Could not start Excel.');

  xlWorkbooks.Open('C:\13. PROCESS CHECKSHEET\' + ProductionOrder."Source No." + '.xlsx');
  xlWorkbook := xlApplication.ActiveWorkbook;
  xlSheets := xlWorkbook.Worksheets;

  FOR I := 1 TO xlSheets.Count DO BEGIN
    xlWorksheet := xlSheets.Item(I);
    xlWorksheet.Activate;

    xlRange := xlWorksheet.Range(xlsCell(14,7));
    xlRange.Value := ProductionOrder."No.";

    xlRange := xlWorksheet.Range(xlsCell(14,8));
    xlRange.Value := FORMAT(ProductionOrder.Quantity);
  END;

  FOR I := 2 TO xlSheets.Count DO BEGIN
    xlWorksheet := xlSheets.Item(I);
    xlWorksheet.Activate;

    IF (FORMAT(xlWorksheet.Name) = 'PROCESS CHECKSHEET') OR (FORMAT(xlWorksheet.Name) = 'Process Checksheet') THEN BEGIN
      xlRange := xlWorksheet.Range(xlsCell(14,7));
      xlRange.Value := ProductionOrder."No.";

      xlRange := xlWorksheet.Range(xlsCell(14,8));
      xlRange.Value := FORMAT(ProductionOrder.Quantity);
    END;
  END;

   xlWorkbook.SaveAs('C:\13. PROCESS CHECKSHEET\temp\' + ProductionOrder."Source No.");
   MESSAGE('Success');
   xlWorkbook.Close(TRUE);
   xlApplication.Quit;
   CurrReport.QUIT;