Hi All,
I want to export the data to an excel sheet,over different worksheets using Excel buffer or any other method(If any). I am able to get the data over a single worksheet but i want the data to spread across multiple worksheets.So,How can i achieve this… Is there any function capable of doing the above… please help me out with this…
Regards,
Prashanth Sanath
No, this can not be doneby default.
But you can add a new function to the Excel-buffer table, in wich you add new sheet to existing workbook.
Can be something like this:
AddSheet()
XlWrkSht := XlWrkBk.Worksheets.Add();
Then you fill in the buffer with data for the 1. sheet, create the sheet and empty the buffer.
Call the new function to have a new sheet added to the workbook.
Fill the buffer with data for the 2. sheet, create the sheet, empty the buffer.
And so on…
I tried adding a new function with
XlWrkSht := XlWrkBk.Worksheets.Add();
and received the following error:
“The automation variable has not been instatntiated. You can instantiate it by either creating or assigning it.”
Do I have to create a new automation variable to hold the new instance of the worksheet.
If so, I do not have that capability.
Thanks,
Phil Lukaszak
I found an error in another part of my program that was causing the error message.
This works fine now.
Thanks for the idea.
Phil Lukaszak
Hello Phil Lukaszak
How did you solve it? I have same problem.
Thanks for reply
HI Phil
How you did this, Can you show us the code
Hello,
Solution:
Globals:
Name DataType Subtype
xlApp Automation ‘Microsoft Excel 8.0 Object Library’.Application
xlBook Automation ‘Microsoft Excel 8.0 Object Library’.Workbook
xlSheetCP Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet
xlSheetCE Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet
xlSheetIC Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet
xlSheet Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet
xlSheetMC Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet
i Integer
x Integer
CODE:
// OPEN THE EXCEL APPLICATION
CREATE(xlApp);
// ALLOW IT TO BE VISIBLE
xlApp.Visible(TRUE);
// CREATE A WORKBOOK WITH ONLY ONE SHEET (WORKBOOK CANNOT BE CREATED EMPTY)
xlApp.Workbooks.Add(1);
// MAKE THE WORKBOOK ACTIVE
xlBook := xlApp.ActiveWorkbook;
// CREATE THE WORKSHEETS (4 OF THEM) WITHIN THE WORKBOOK, AND NAME EACH ONE ON IT’S WORKSHEET TAB
xlSheetCP := xlBook.Worksheets.Add;
xlSheetCP.Name := ‘Customer Sales - Parts’;
xlSheetIC := xlBook.Worksheets.Add;
xlSheetIC.Name := ‘Inter Company’;
xlSheetCE := xlBook.Worksheets.Add;
xlSheetCE.Name := ‘Customer Sales - Equipment’;
xlSheetMC := xlBook.Worksheets.Add;
xlSheetMC.Name := ‘Major Code 99’;
// Remove the ‘original’ sheet named ‘Sheet1’ - keeping only MY sheets
xlApp.DisplayAlerts(FALSE);
xlSheet := xlBook.Worksheets.Item(‘Sheet1’);
xlSheet.Delete;
// CALL FUNCTION TO SETUP SHEETS
SheetSetup;
SheetSetup function:
// SET THE COUNTER TO BEGIN AT THE FIRST SHEET, AND END AT LAST
i := 1;
x := (xlBook.Worksheets.Count);
// LOOP THROUGH EACH SHEET - OUTPUTTTING COLUMN HEADINGS & FORMATTING
REPEAT
xlSheet := xlBook.Worksheets.Item(i);
xlSheet.Range(‘A3’).Value := ‘Item’;
xlSheet.Range(‘B3’).Value := ‘Description’;
xlSheet.Range(‘C3’).Value := ‘Amount’;
xlSheet.Range(‘C:C’).Columns.NumberFormat := ‘#,##0.00’;
//…AND SO ON AND SO ON
i := i + 1;
UNTIL i > x;
Regards,
Sami Mohammed.