1. Sankar AR
  2. PowerBuilder
  3. Thursday, 8 October 2020 07:58 AM UTC

Regarding Ole excel - powerbuilder script

I Want to Find the last column Cell in a excel(Result should be for ex: Y6 or Z10)

using the below usedrange, script i can able to get the total columns and total rows

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

 

My question is , i want to find the last row , last column cell (for ex: last row last column will be Y6 or Z10)

please do the needful

Sankar

 

 

John Fauss Accepted Answer Pending Moderation
  1. Friday, 9 October 2020 13:58 PM UTC
  2. PowerBuilder
  3. # 1

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

Comment
  1. Sankar AR
  2. Friday, 9 October 2020 19:33 PM UTC
Thank you John, will check
  1. Helpful
There are no comments made yet.
Sankar AR Accepted Answer Pending Moderation
  1. Friday, 9 October 2020 07:54 AM UTC
  2. PowerBuilder
  3. # 2

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)

Comment
  1. René Ullrich
  2. Friday, 9 October 2020 09:04 AM UTC
Sorry, instead of Cells("A2") use Cells(2,1).



Instead of UsedRange you may try one of this:

- get last row (also for column): UsedRange.SpecialCells(xlCellTypeLastCell).row

- last row with a value in a special column (in this example: column 4): Cells(Rows.Count, 4).End(xlUp).Row

- for columns (here for row 3): Cells(3, Columns.Count).End(xlToLeft).Column

- last row (also for column) within a range: Range("B1:F10").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row



Use the corresponding values instead the xl-Constants (e.g. xlCellTypeLastCell = 11)
  1. Helpful
  1. Sankar AR
  2. Saturday, 10 October 2020 05:38 AM UTC
Hi Rene sir,



is there any alternate ways to get the all rows and all columns From Excel and setitem to datawindow control dynamically using OLE object.



i have tried some other ways, like data window import file only accept the limited formats(CSV, tab delimeted text file, XML file), so that tried to convert the Excel File to the above allowed formats.



but it wont help to me



Regards,

Sankar
  1. Helpful
  1. Miguel Leeuwe
  2. Saturday, 10 October 2020 08:47 AM UTC
Be warned that there's a BUG with the "specialCells" when you have more than 16,385 rows:

https://bettersolutions.com/excel/cells-ranges/vba-special-cells.htm



SpecialCells might not be available in older versions of Excel.



See also:

https://bettersolutions.com/excel/cells-ranges/vba-finding-last-row-column.htm

  1. Helpful
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 9 October 2020 04:46 AM UTC
  2. PowerBuilder
  3. # 3

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é

Comment
There are no comments made yet.
Sankar AR Accepted Answer Pending Moderation
  1. Thursday, 8 October 2020 16:59 PM UTC
  2. PowerBuilder
  3. # 4

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

 

 

 

 

 

 

 

 

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 8 October 2020 10:53 AM UTC
  2. PowerBuilder
  3. # 5

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é

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.