topic Re: Validate Excel Sheet Name in Application Development and Automation Discussions

Validate Excel Sheet Name

vamsi_pokala — Mon, 22 Jun 2009 14:35:39 GMT

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

How to Validate the WorkSheet Name?

Re: Validate Excel Sheet Name

Former Member — Mon, 22 Jun 2009 15:38:45 GMT

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.

Re: Validate Excel Sheet Name

Former Member — Mon, 22 Jun 2009 19:20:22 GMT

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,

Re: Validate Excel Sheet Name

vamsi_pokala — Tue, 23 Jun 2009 11:43:51 GMT

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.

Re: Validate Excel Sheet Name

Former Member — Tue, 23 Jun 2009 11:56:14 GMT

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

Re: Validate Excel Sheet Name

Former Member — Tue, 23 Jun 2009 11:57:15 GMT

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.

Re: Validate Excel Sheet Name

Former Member — Tue, 23 Jun 2009 11:59:39 GMT

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