Updating Specific Named Excel Sheets

rbmafrica

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

DigiTecKid

Hello Robert,

In the code example you gave ,I don't see the "autoSheet" automation created. That's causing your error. But really you're using the Excel Buffer table functions incorrectly. You open the work book and create a sheet but that does not instantiate those automations in your code, only inside the "recExcelBuf" variable.

I've done a quick rewrite of that code you submitted. Trying going down this path...

Make sure your variable for the Excel Buffer table (recExcelBuf) is set as a temporary table. Look at the properties for the variable.

*****
OnPreReport
//Excel Initialiser
IF booMakeExcel THEN BEGIN
IF ((txtFileName = '') OR (txtSheetName = '')) THEN
ERROR('You must specify the excel file\'+
'and worksheet to Update');
//Don't create open the workbook or create the sheet in the beginning, but at the end.
//(which moves the data from the Excel Buffer into the Excel spreadsheet)
*****

*****
OnPreDataItem
//txtRow changed to integer variable
intRow := 5
END;
*****

*****
OnAfterGetRecord
//Export to Excel
IF booMakeExcel THEN BEGIN
//Created intCol integer variable
intCol := 1;

//See New function created in this report
EnterCell(intRow, intCol, FORMAT("TIN No."), '@', FALSE);
intCol += 1;
EnterCell(intRow, intCol, FORMAT("First Name"+''+"Middle Name"+''+"Last Name"), '@', FALSE);
intCol += 1;
EnterCell(intRow, intCol, FORMAT(PeriodRec."Start Date"), '@', FALSE);
intCol += 1;
EnterCell(intRow, intCol, FORMAT(PeriodRec."End Date"), '@', FALSE);
intCol += 1;
EnterCell(intRow, intCol, Amt[1], '@', FALSE); //Basic Salary
intCol += 1;
EnterCell(intRow, intCol, Amt[2], '@', FALSE); //Payment in lieu of Leave
intCol += 1;
EnterCell(intRow, intCol, Amt[3], '@', FALSE); //Other Valued Benefits - Gratuity

intRow += 1;
END;
*****

*****
OnPostReport
//Open the Excel spreadsheet and push in the data
IF booMakeExcel THEN
BEGIN
recExcelBuf.OpenBook(txtFileName,txtSheetName);

//This function will push the data from the Excel Buffer records
//into the Excel workbook you're creating
recExcelBuf.CreateSheet(txtSheetName,'',COMPANYNAME,USERID);

recExcelBuf.GiveUserControl;
END;
*****

***** New Function *****
EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];NumberFormat : Text[30];Bold : Boolean)
//This function enters records into your temp table "recExcelBuf" that will be
//used later to create the spreadsheet
IF NOT booMakeExcel THEN
EXIT;
recExcelBuf.INIT;
recExcelBuf.VALIDATE("Row No.",RowNo);
recExcelBuf.VALIDATE("Column No.",ColumnNo);

//I'm pretty sure the '@' symbol entered here makes the column automatically expand
//to the width of the value in the cell
IF NumberFormat = '' THEN
recExcelBuf."Cell Value as Text" := CellValue
ELSE BEGIN
recExcelBuf.Formula := CellValue;
recExcelBuf.NumberFormat := NumberFormat;
END;

recExcelBuf.Bold := Bold;
recExcelBuf.INSERT;

rbmafrica

Thanks DigTecKid!

Let me try to implement your suggestions and revert with feedback.

Regards,

Robert