Updating Specific Excel Named Sheets

Business Central/NAV Developers Forum
1

Hi All!. I am trying to update a complex spreadsheet, with macros and rules. I need to be able to obtain a sheet, and move to specific cells to dump data. My main problem is picking and getting control of a sheet in the file. So far, I have added navigation options to the report and am able to navigate and select a file on the drive, and then select a epcific sheet in the file, but that is about it. An attempt to update throws this error:

This automation variable has not been instantiated. You can instantiate it by either creating or assigning it.

Here are the sections of my code:

Variables:
i Integer
booMakeExcel Boolean
autoExcel Automation ‘Microsoft Excel 12.0 Object Library’.Application
autoBook Automation ‘Microsoft Excel 12.0 Object Library’.Workbook
autoRange Automation ‘Microsoft Excel 12.0 Object Library’.Range
autoSheet Automation ‘Microsoft Excel 12.0 Object Library’.Worksheet
txtRow Text 30
txtFileName Text 250
txtSheetName Text 250
recExcelBuf Record Excel Buffer

OnPreReport
//Excel Initialiser
IF booMakeExcel THEN BEGIN
IF ((txtFileName = ‘’) OR (txtSheetName = ‘’)) THEN
ERROR(‘You must specify the excel file’+
‘and worksheet to Update’);
txtRow :=FORMAT(5);

OnPreDataItem
//Initialise Excel
IF booMakeExcel THEN
BEGIN
CREATE(autoExcel);
recExcelBuf.OpenBook(txtFileName,txtSheetName);
recExcelBuf.CreateSheet(txtSheetName,’’,COMPANYNAME,USERID);
END;
txtRow := ‘5’;
END;

OnAfterGetRecord
//Export to Excel
IF booMakeExcel THEN BEGIN
autoSheet.Range(‘A’+ txtRow).Value := FORMAT(“TIN No.”);
autoSheet.Range(‘B’+ txtRow).Value := FORMAT(“First Name”+’’+“Middle Name”+’’+“Last Name”);
autoSheet.Range(‘C’+ txtRow).Value := FORMAT(PeriodRec.“Start Date”);
autoSheet.Range(‘D’+ txtRow).Value := FORMAT(PeriodRec.“End Date”);
autoSheet.Range(‘E’+ txtRow).Value := Amt[1]; //Basic Salary
autoSheet.Range(‘I’+ txtRow).Value := Amt[2]; //Payment in lieu of Leave
autoSheet.Range(‘O’+ txtRow).Value := Amt[3]; //Other Valued Benefits - Gratuity
txtRow := INCSTR(txtRow);
END;

recExcelBuf.GiveUserControl;

What am I missing?

Robert