Validate Excel Sheet Name

[vamsi_pokala]

In the Selection Screen of a Report there is field to accept Excel File WorkSheet Name.

How to Validate the WorkSheet Name?

[Former Member]

Hi Vamsi,

If you want to validate the excel file stored in the presentation server(your PC) then make use of method

file_exist of class cl_gui_frontend_services

call method cl_gui_frontend_services=>FILE_EXIST

exporting

file = file_path

receiving

result = rc.

Regards,

Babul.

[Former Member]

hey.. you mean to validate whether the file of EXCEL type or not??..

if that is here is the simplest way..

DATA LV_FILE type string.

SPLIT P_FILE AT '.' INTO LV_FILE DUMMY.

if DUMMY = 'xls' OR dummy = 'xlsx'.

..proceed..

endif.

hope..this helps u..

Regards,

KC

[vamsi_pokala]

I want to Validate the WorkSheet Name in the Excel File. For example: If the Name of the WorkSheet in the Excel File is "Sheet1" and I enter "Sheet2" in the Selection Screen, then I want to throw an error message.

[Former Member]

Hi

use this code ...

 
INCLUDE ole2incl.

data : application type ole2_object,
       workbook type ole2_object,
       worksheet type ole2_object,
       l_sheet_name type string.

CREATE OBJECT application 'Excel.Application'.

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Open'
  EXPORTING
    #1 = 'C:\Book1.xls'.

GET PROPERTY OF  application 'ACTIVESHEET' = worksheet.

GET PROPERTY OF worksheet 'Name' = l_sheet_name.

"now check sheet name

Amitava

Edited by: Amitava De on Jun 23, 2009 5:30 PM

[Former Member]

Hi,

You will have to make use of OLE-Aotumation for this.

try this out

*--Get the No of sheets in excel file
*  start the excel application
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  CALL METHOD OF excel 'WORKBOOKS' = workbooks.
  SET PROPERTY OF excel  'VISIBLE' = 0.
  CALL METHOD OF workbooks 'OPEN' EXPORTING
            #1 = p_fname.
*-- get the no of worksheets in excel file
  CALL METHOD OF excel 'SHEETS' = worksheets.
  GET PROPERTY OF worksheets 'COUNT' = no_of_sheets.
  FREE OBJECT worksheets.
  CALL METHOD OF workbooks 'CLOSE'.
  CALL METHOD OF excel 'QUIT'.

  FREE OBJECT excel.
  FREE OBJECT worksheets.
  FREE OBJECT workbooks.

*--Read each sheet one at a time
  DATA : l_activesheet TYPE i.
*--Open the excel
    CREATE OBJECT excel 'EXCEL.APPLICATION'.
    CALL METHOD OF excel 'WORKBOOKS' = workbooks.
*--Not visible
    SET PROPERTY OF excel  'VISIBLE' = 0.
    CALL METHOD OF workbooks 'OPEN'
              EXPORTING  #1 = p_fname.  "p_fname is the filename from selection screen
*--Activate sheet n
    CALL METHOD OF excel 'Worksheets' = sheet
              EXPORTING  #1 = l_activesheet.
    GET property of sheet 'Name' = name. "Name will give you the Sheet name embedded in excel
    call method of sheet 'Activate'.
    GET PROPERTY OF excel 'ACTIVESHEET' = sheet.
*--Save the excel again as new file
    CALL METHOD OF  sheet 'SAVEAS'
              EXPORTING  #1 = p_fname1
                         #2 = 1.
    FREE OBJECT sheet.
    CALL METHOD OF workbooks 'CLOSE'.
    CALL METHOD OF excel 'QUIT'.

    CALL METHOD OF workbooks 'CLOSE'.
    CALL METHOD OF excel 'QUIT'.

    FREE OBJECT excel.
    FREE OBJECT worksheets.
    FREE OBJECT workbooks.

 Now you can validate with NAME

Hope this helps.

[Former Member]

Hi,

try this one. Thats works fine for me. Looking for OLE in the SAP-Help.

type-pools ole2 .

data:
  asheet type ole2_object.

*   Get active Sheet-Name
    call method of excel 'ACTIVESHEET' = asheet.
    get property of asheet 'NAME' = r_tabname.

*  Select Sheet
    call method of excel 'WorkSheets' = sheet
      exporting
        #1 = i_tabname.
    call method of sheet 'Activate'.

Regards

Andreas