Greetings, Sankar - 

This appears to be an Excel question, not a PowerBuilder question. Have you inquired in any Excel forums?

Once you can determine the proper way to query the minimum number of rows and columns that contain data in an Excel spreadsheet to your satisfaction, you should be able to use methods and properties in the Excel object model (via OLE) from a PB application to accomplish your objective.

Regards, John

Hi Sir,

Thanks a lot for your valuable inputs. sorry to ask the question

if i am using the below line i am getting the cumulative column value , for example There is 6 rows including the header, A is the starting column , Y is the ending column in 6th row, the result should be y6 

but it is returning y102

As for the 6 rows Y6 is my ending column

but this used range column count is returning 102 is the last column

ll_xls_cols = excel.worksheets(1).Usedrange.columns.count

but requirement is it should  return the last column as Y6 irrespective of the data value availability or not.

This function "of_get_excel_columnname", last column i am hard coding, is it possible to make it dynamic?

  ls_column = String (CHAR (ASC ("Y") + ll_temp - 1)) + ls_column

Code:

ls_range = "A2:" + of_get_excel_columnname(ll_xls_cols) + string (ll_xls_rows)

oleobject lole_startcell, lole_endcell
lole_startcell = excel.Worksheets(1).Cells("A2")
lole_endcell = excel.Worksheets(1).Cells(ll_xls_rows, ll_xls_cols)
excel.Worksheets(1).Range(lole_startcell, lole_endcell).Copy

 while executing the below line -. ole run time error is coming

"Error calling external object function cells at line 46 in clicked event of object 

lole_startcell = excel.Worksheets(1).Cells("A2")
lole_endcell = excel.Worksheets(1).Cells(ll_xls_rows, ll_xls_cols)

If the variables ll_xls_cols and ll_xls_rows have the correct number of cols and rows as you need, then the range (in format like "A2:Y6") is:

ls_range = "A2:" + of_get_excel_columnname(ll_xls_cols) + string (ll_xls_rows)

BTW: You may get the range also without using of_get_excel_columnname:

oleobject lole_startcell, lole_endcell
lole_startcell = excel.Worksheets(1).Cells("A2")
lole_endcell = excel.Worksheets(1).Cells(ll_xls_rows, ll_xls_cols)
excel.Worksheets(1).Range(lole_startcell, lole_endcell).Copy

Is this that what you want to know?

One more hint:
Be carefull with UsedRange property! The used range is not limited by the currently last filled cell! Also empty rows and columns at the end may be part of the used range (e.g. after deletion of content)!

HTH,

René

Hi Rene Sir,

Thanks a lot for valuable inputs.

My Requirement is , i am copying the values from Excel file to Datawindow control using the below script.

Range always starts with A2 , starting range is constant and no change.

but end  Range will be dynamic, one excel will have Y6 and another excel will have Z20.

Instead of open the excel file manually and check about the end range, want to find the end range in run time/dynamically.

excel.Worksheets(1).Range("A2:Y6").Copy

as per your suggestion we need to provide the end column header? For Ex: Y / A

It would be great if i get more details. Thanks a lot in advance!

Script used from google:

OLEObject excel

Integer li_RetValue, li_rtn
Boolean lb_sheet_rtn
Long ll_cnt
String ls_ClipBoard

// Varibles for File Name,path

string docpath, docname[]
integer i, li_cnt, li_filenum
long ll_xls_cols,ll_xls_rows

// Get file path

GetFileOpenName("Select File", docpath, docname[], "DOC", "Excel Files (*.xlsx),*.xlsx," + "All Files (*.*), *.*", "C:\Program Files\Sybase", 18)

if docpath='' then return

//MessageBox("Document Path ", docpath)

excel = create OLEObject

li_rtn = excel.ConnectToNewObject("excel.application")
IF li_rtn <> 0 THEN
MessageBox('Excel error','can not run Excel Program')
DESTROY excel
RETURN 0
END IF

excel.WorkBooks.Open(docpath )
excel.Application.Visible = false
excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize

lb_sheet_rtn = excel.worksheets(1).Activate

ls_ClipBoard = Clipboard() //Store current clipboard Data

// To Find the No Of Rows and Columns in the excel sheet

ll_xls_cols = excel.worksheets(1).Usedrange.columns.count
ll_xls_rows = excel.worksheets(1).Usedrange.rows.count
ll_xls_rows = ll_xls_rows -1 // Considering row 1 has headers

MessageBox("Rows and Cols In the Excel",string(ll_xls_rows)+ " Rows and "+string(ll_xls_cols)+" Columns Will be imported to application")

// To get the value of a cell in the excel sheet
// You can do your validations on the Excel Cells data here based on your business requirements
MessageBox("A1 cell value",string(excel.worksheets(1).cells(1,1).value)) // First Row First Column in the excel sheet
MessageBox("A2 cell value",string(excel.worksheets(1).cells(2,1).value)) // First Row First Column in the excel sheet
//excel.woksheets(1).Range(
String ls_col
ls_col=of_get_excel_columnname(ll_xls_cols)
Messagebox('ls_col value',ls_col)
excel.Worksheets(1).Range("A2:Y6").Copy // I have hard coded the Cells here your can change by using no of rows and columns and concatinating them
// copy to clipboard - Take enough Range. .. Or use the UsedRage property. If data ha header row, use correct statring range.. (B1:... )
ll_cnt = dw_1.importclipboard()

Hi Sankar,

AFAIK there is no direct way to go that. There are several way using excel to convert the column number into the letter(s).

Here a way with PowerBuilder:

string of_get_excel_columnname (long al_column)

// converts the excel column number (e.g. 1) into the column name ("A")

string ls_column
constant long ll_letters = 1 + ASC ("Z") - ASC ("A")
long ll_temp

DO WHILE al_column > 0
  ll_temp = 1 + MOD (al_column - 1, ll_letters)
  al_column = (al_column - 1) / ll_letters

  ls_column = String (CHAR (ASC ("A") + ll_temp - 1)) + ls_column
LOOP

return ls_column

HTH,

René