Options

Updating Specific Named Excel Sheets

rbmafricarbmafrica Member Posts: 6
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
IT Consultant
RBM Systems Consult Ltd
P. O. Box 5473 Kampala, Uganda

"You have not started, until you start"

Comments

  • Options
    DigiTecKidDigiTecKid Member Posts: 46
    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;
  • Options
    rbmafricarbmafrica Member Posts: 6
    Thanks DigTecKid!

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

    Regards,

    Robert
    IT Consultant
    RBM Systems Consult Ltd
    P. O. Box 5473 Kampala, Uganda

    "You have not started, until you start"
Sign In or Register to comment.