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()