Paste to Last Row in Column

[quarkrad]

I have been learning ooO coding and trying to amend working macros to gain experience. This is the last section of a macro that copies data to another spreadsheet. At my novice level I'm just about hanging on but I think I can follow what is happening and why. I am trying to amend this last section so that the LastRow identified is only for ColumnA rather than the whole spreadsheet. I have added oCol = oSheetB.getColumns().getByIndex(0) but it does not work. To be honest I have tried lots of added coding and so far nothing has worked. Any help appreciated.

Code: Select all

oSheetB = oDocB.getSheets.getByIndex(0)
oCol = oSheetB.getColumns().getByIndex(0)
c = oSheetB.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow +1
targetcell= oSheetB.getCellByPosition(0,LastRow)
oDocB.CurrentController.Select(targetcell)
oFrameB = oDocB.CurrentController.Frame
oDispatcher.executeDispatch(oFrameB, ".uno:Paste", "", 0, Dummy())

[Zizi64]

I have added oCol = oSheetB.getColumns().getByIndex(0) but it does not work. To be honest I have tried lots of added coding and so far nothing has worked. Any help appreciated.

Do you using an object inspection tool like the MRI or the XrayTool? I suggest you to download, install and use one of them. Then you will able to examine the programming objects: what properties and methods they have.

[quarkrad]

I've installed the mri tool and read a tutorial on it too but to be honest I'm not really any further forward.

[RoryOF]

Its a complicated tool - you have to play with it until you become familiar with it.

Edit: Detailed MRI documentation is at https://github.com/hanya/MRI/wiki

[quarkrad]

Could you help with some tool pointers re keeping the search (last row) to a specific column?

[Zizi64]

Could you help with some tool pointers re keeping the search (last row) to a specific column?

The row number of the last used cell on the sheet is not same as the row number of the last used row in the desired column in your document?

Can you upload an ODF type sample file here?

[Lupp]

For what reason do you want to assign a Column object ('ScTableColumnObj') to a variable?
As far as I can remember I never did it. Obviously I never needed it.
You also don't seem to need it: Once assigned the column object to a variable, that variable is never used.

For me all the asignments in the following code work:

Code: Select all

Sub Test
theSheet = ThisComponent.Sheets(0)
theCol_1 = theSheet.Columns(1)
theCol_2 = theSheet.Columns.GetByIndex(2)
theCols   = theSheet.Columns
theCol_3 = theCols(3)
theRow_2 = theSheet.Rows(2)

theCol_4_Name  = theCols.ElementNames(4)
REM The following statement will not work in AOO because there still
REM is not implemented the A:Z syntax for ranges consisting of complete columns.
theCol_4_Range = theSheet.GetCellRangeByName(theCol_4_Name & ":" & theCol_4_Name)
End Sub

A strange observation: In AOO and in LibO as well it takes a long time (around 30 s on my old computer) to open one of the Col_ objects for inspection in the IDE. The process seems to use much RAM, too.
Experimentally I included the Row object. It was not afflicted by the mentioned specialties.

The final way in my code (not available in AOO) to get access to a column works differently. It doesn't actually return a Column object but a CellRange object. In the IDE it opens immediately for inspection.

[RoryOF]

Perhaps the col object has to go through some internal process of examining each of the cells of the column (all 1048576 of them), whereas a row object has many less components.

[Villeroy]

Some helper functions.
getCurrentRegion gets adjacent non-empty cells around a given range.
getUsedRange gets the used range on a given sheet.
getActive cell gets the active cell of a given controller (view, window), usually doc.getCurrentController()
getOffsetRange does the same as the OFFSET sheet function.
getRangeByAddress takes a document, a sheet or a range and a range address and returns a range object
getCellByAddress takes a document, a sheet or a range and a cell address and returns a cell object

Code: Select all

Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function

Function getUsedRange(oSheet)
Dim oRg
	oRg = oSheet.createCursor()
	oRg.gotoStartOfUsedArea(False)
	oRg.gotoEndOfUsedArea(True)
	getUsedRange = oRg
End Function

Function getCurrentRegion(oRange)
Dim oCursor
	oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
	oCursor.collapseToCurrentRegion
	getCurrentRegion = oCursor
End Function

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function

REM pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
REM returns empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

REM pass a spreadsheet-document, sheet or range together with a c.s.s.table.CellAddress
REM return empty if oAddr out of bounds or wrong obj
Function getCellByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getCellByAddress = oSheet.getCellByPosition(oAddr.Column,oAddr.Row)
exit function
nullErr:
	getCellByAddress = Null
End Function

Nevertheless it does not make sense to paste below the used range. Simply rows insert on top or anywhere within the range and fill out the new cells. This has the advantage that all references to the list range will expand automatically, e.g. =SUM(A1:A99) becomes =SUM(A1:A100) after insertion of a new row. Any range names, charts etc. adjust likewise.

[Lupp]

@RoryOF: Yes, I thought of that, but I couldn't find plausibility for it. The col_4_Range object of my example has also 2^20 cells and supports more services. Nonetheless it opens immediately in the IDE. A spreadsheet object has 2^30 cells. But surely there must be something behind my observations.

OK. I needn't understand everything... Should be glad that I understand current politics and trends so completely...
Thanks for your interest.

[quarkrad]

For my specific need I cannot see data in column A going beyond row 500, so is there a way of limiting the size of the search to say 5000 or 10000 rows in column A to limit the search and save time - or am I speaking rubbish?

[Villeroy]

The empty cells are not stored in the spreadsheet. They are just offered as availlable free space whenever you open a spreadsheet.
Every spreadsheet user should be able to use navigation keys which are the same in Excel:
The Home key jumps to the column A of the current row.
The End key jumps to the last used column of the current row.
Ctrl+Arrow key jumps from a content cell to the next gap or from a blank cell to the next content in the respective direction.
Ctrl+End jumps to the last used cell.
All the above combined with the Shift key expand the selection on their way.

Ctrl+End, Down, Home goes to the first cell in the row below the used range.

getting the next empty cell programmatically:

Code: Select all

REM calling helper function getUsedRange
rg = getUsedRange(oSheet)
addr = rg.getRangeAddress()
cell = oSheet.getCellByPosition(addr.StartColumn, addr.EndRow +1)

this works even if the used range does not start in column A, say used range is F10:K9999.

[JohnSUN-Pensioner]

The empty cells are not stored in the spreadsheet.

This is true if you do not do the stupid thing that beginners of spreadsheets often do - select the entire column or a few columns and apply some complicated format to it. In this case, Calc will have to write each of these empty cells into the file, accompanied by a note like "green background, red border, numeric format with three digits after the comma, aligned to the left along the top edge of the cell".
The file size increases many times, its opening and saving speed slows down
Do not overuse formatting cells, which do not have data.