Ask Your Question

find first empty row

asked 2018-03-06 22:49:17 +0200

Goc2 gravatar image

updated 2018-04-04 15:49:27 +0200

I am adding to this thread because in this original post one element I don't think was clearly shared was that this is to copy/paste three cells from the main spreadsheet (main.ods) to another spreadsheet (Summary.ods).

The need is still true for the advancing content one row in the Summary.ods sheet as shared in the original post, think I can make this happen with help offered below once I have a successful copy/paste to (Summary.ods) spreadsheet from (main.ods)

I am aware that other methods than "copyRange" must be used to copy data between different documents in this case (main.ods) and (Summary.ods).

Below is what I have for copying and pasting into a new spreadsheet.

It produces the "BASIC runtime error. Object variable not set."

I have tried numerous things to set the "oDoc2" variable that is currently commented out in the macro below.

This line causing error - rng = oDoc2.Sheets(0).getCellRangeByName("A1:A3")

Sub test

Dim oDoc1
Dim oDoc2
Dim oSheets
Dim oSheet

oDoc1 = Thiscomponent
'oDoc2 = "c:\Summary.ods" 

oDispatcher = createUnoService("") 
oFrame1 = oDoc1.CurrentController.Frame 
oSheet = oDoc1.Sheets(0) 
rng = oSheet.getCellRangeByName("AP25:AR25") 
oDispatcher.executeDispatch(oFrame1, ".uno:Copy", "", 0, Array()) 
rng = oDoc2.Sheets(0).getCellRangeByName("A1:A3") 
oFrame2 = oDoc2.CurrentController.Frame 
oDispatcher.executeDispatch(oFrame2, ".uno:Paste", "", 0, Array())  

End Sub

Original Post

I am hoping someone can lead me to a sample of the following

I am wanting to copy content (with a button) from 3 cells into a new sheet with three columns. I can get the content to end up in example cells E5 F5 and G5.

My problem is when the content gets entered again I need it to be entered into the next free row E6, F6 and G6.

I am currently overwriting the content in E5, F5 and G5 and getting the message "You are pasting data into cells that already contain data. Do you really want to overwrite the existing data?"

What's the code to move the paste into cells down one row?

Below is what I currently have.

sub Bonus
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$AM$25"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new
args3(0).Name = "Nr"
args3(0).Value = 10

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new
args4(0).Name = "ToPoint"
args4(0).Value = "$E$5"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(0) as new ...
edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-03-07 14:25:51 +0200

Lupp gravatar image

updated 2018-04-04 19:11:46 +0200

If you are interested in a second thought, you may also study the code contained in this attached example.

There are also different approaches based on formulas. If interested you may throw a glance at the example I attached to my answer in this thread.

Edit1 regarding the recent update of the question:
Are you aware of the fact that a file's pathname is very different from an object of a LibreOffice document class? A spreadsheet document is mainly qualified by supporting the service which to do you cannot expect a string. To create the mentioned object knowing a pathname of the respective file you need to use the method LoadFromURL provided by the StarDesktop. See the following two lines of BASIC code as an example:

fN = "C:\Users\Myself\Documents\aFolder\aSubFolder\MyFile.ods"

You should probably read the fanous texts by Andrew Pitonyak to. You can get them here.

edit flag offensive delete link more


Thanks @Lupp! That 152042911012607 file work! I will save those subs, I may be able to use them later. The 15201704636384773 file, I see the formula's ok. You may be interested in seeing how you have help me this last month, I have played with this file for 3 years! No password and basically just recorded subs. It has worked well, except for a few harder formulas, that you have helped with, that cleaned up a lot. Its like a game, a pass time, to me. Again. THANK

Mark McLean gravatar imageMark McLean ( 2018-03-07 19:24:07 +0200 )edit

Thank you Lupp. I will have a look at these approaches.

Goc2 gravatar imageGoc2 ( 2018-03-07 19:37:46 +0200 )edit

Forgot...I have Marcos on a hidden sheet to be copy to a Module. I was having trouble copying file and opening on other computers, that seem to fix it. ??

Mark McLean gravatar imageMark McLean ( 2018-03-07 20:30:12 +0200 )edit

Lupp, I have done some testing on the sample you sent with the two buttons.

Within my multi-sheet file, when the macros are run they jump to the first sheet in the file.

I tried adjusting the pSheetN =1 setting to control the page I land on but get an error. Also, I am having trouble deciphering how the target cell is being defined. Both macros currently land in the "m" column of he sheet being redirected to.

Goc2 gravatar imageGoc2 ( 2018-03-07 22:39:13 +0200 )edit

I tried oDoc2 = thisComponent.getSheets and some other functions but couldn't get anything to load the Summary.ods spreadsheet. I was 99.9% sure the path name would not do it by itself, I just happened to leave it in the sample, that's why it was commented out.


oDoc1 = Thiscomponent
oDoc2 = StarDesktop.LoadComponentFromURL(ConvertToURL(fN),"_blank",0,Array())
fN = "c:\Summary.ods"

It produces: runtime error. Unsupported URL

Summary.ods is in root of C

Goc2 gravatar imageGoc2 ( 2018-04-04 19:52:39 +0200 )edit

Did you really assign the string constant to fN after calling LoadComponentFromURL() with fN inside?

Lupp gravatar imageLupp ( 2018-04-04 20:22:41 +0200 )edit

I Did. Duh...


oDoc1 = Thiscomponent
fN = "C:\Summary.ods"
oDoc2 = StarDesktop.LoadComponentFromURL(ConvertToURL(fN),"_blank",0,Array())

Calling out this line "Object variable not set."

rng = oDoc2.Sheets(0).getCellRangeByName("A1")
Goc2 gravatar imageGoc2 ( 2018-04-04 20:33:05 +0200 )edit

Sorry! I cannot confirm this behaviour. My examples work as expected.
One guess: If the file was already opened and, playing with the Sub, you open it a second time you get a respective message. There is also the offer to work on a copy instead of on the document itself. If you accept that offer your oDoc2 is gone. That should be expected.

Lupp gravatar imageLupp ( 2018-04-04 21:19:04 +0200 )edit

Lupp, Are you saying you ran the macro using the code in this post and it worked? I will try setting up with bare bones files for both documents to see if presents the same problem. Is this what you meant by "work on a copy" instead of the document itself?

Can you explain how the .Sheets impacts this line rng = oDoc2.Sheets(0).getCellRangeByName("A1") if it is declared as Dim Sheets should this suffice? It isn't causing the "object variable not set" message is it?

Goc2 gravatar imageGoc2 ( 2018-04-04 21:37:06 +0200 )edit

I feel puzzled know. Thought my statements were clear.
"... you ran the macro using the code in this post and it worked?" Yes. This regarding the reserve that I didn't create a file Summary.ods in the root of my file system. I worked with a file in a standard path for my everyday work. I would expect you also need administrator privileges to place an ordinary file in C:\, and opening it later may bring up the "work on a copy" offer.

Lupp gravatar imageLupp ( 2018-04-04 22:35:13 +0200 )edit

answered 2018-03-07 15:13:56 +0200

Goc2 gravatar image

Mark, To preface this, the content is being copied to a new sheet (with a button). I can address it after getting the dynamic row location remedied.

When running this as its on sub (at the bottom of my original code) it errors on this line. Dim oColumn As Object : oColumn = oSheet.Columns.getByIndex( iColumnIndex )

As a test I replaced (iColumnIndex) with (cColumnIndex) to see its results. It doesn't error with (cColumnIndex) but the active cell ends up at the very bottom of the sheets "a" column.

edit flag offensive delete link more


I know little of the macros's, mainly "record macro"! But my test, and how I use it, is where ever active cell is, it will go to bottom of column and activates the empty cell, under the last cell with data in it. On what ever sheet I have it on! Your welcome to look at it! Its a lot, but sub is at top, but not in Basic, I hide it on a hidden sheet, names Macro1, because if I share it, it give warnings about Macros if it is in a Modules, so paste to module .

Mark McLean gravatar imageMark McLean ( 2018-03-07 15:57:24 +0200 )edit

Thank you Mark but the link is not resolving.

Goc2 gravatar imageGoc2 ( 2018-03-07 16:52:34 +0200 )edit

Sorry about that. This should work. Or.

Mark McLean gravatar imageMark McLean ( 2018-03-07 18:28:35 +0200 )edit

Mark, I am tired and made an off the cuff request (below) , Please disregard. I am now sifting through the macros. Thank you.

Thank you Mark. Can you steer me to a sheet in your fie where I can see what we've been discussing in action?

Goc2 gravatar imageGoc2 ( 2018-03-07 19:47:51 +0200 )edit

My Macros, are not working till installed, making the file safe. There Hidden! R. click on any sheets tab at bottom. click "show sheet". open "Macro1", sheet. the sub is top one! To install them, Copy & paste. I put directions at the top of that "Macro1" sheet. It was not off cuff. I like helping.

Mark McLean gravatar imageMark McLean ( 2018-03-07 20:15:15 +0200 )edit

This one has macros on so buttons will work, not sure if it will down load, that's why I hid macro's cause i had trouble downloading with macros on file. Also sheets are locked but no password needed, just r. click on tab to unlock.

Mark McLean gravatar imageMark McLean ( 2018-03-07 20:50:52 +0200 )edit

If IsMissing(pSheetN) Then pSheetN = ThisComponent.CurrentController.ActiveSheet.RangeAddress.Sheet + 1
to make the functions applicable to the active sheet
and make the parameter pEvent of gF() and gL() Optional if you intend to call the Sub not exclusively by an event, but via Tools > Macros > Run probably.
Also note that the calling by a mousebutton_release is primitive and does not check if th release was inside the button. (AndSoOn)

Lupp gravatar imageLupp ( 2018-03-07 23:17:40 +0200 )edit

answered 2018-03-07 12:12:13 +0200

updated 2018-03-07 13:14:07 +0200

I had same problem. I found answer here, somewhere, from a question another ask. It has worked well for me by running it at end of sub by the call command. call Last_Cell_in_Column I've no clue what all the sub is doing, saying, but it works!

Sub Last_Cell_in_Column( Optional iColumnIndex )
REM Moves the cell cursor to 1 cell below the last used Cell in the specified Column in the Active Sheet.
REM <iColumnIndex> : Zero-based index of the Column whose cell should be selected; Leave empty for the Current column.
    Dim oDoc As Object      : oDoc    = ThisComponent
    If IsMissing( iColumnIndex ) Then iColumnIndex = oDoc.CurrentSelection.RangeAddress.StartColumn
    Dim oSheet As Object    : oSheet  = oDoc.CurrentController.ActiveSheet
    Dim oColumn As Object   : oColumn = oSheet.Columns.getByIndex( iColumnIndex )
    Dim oRanges As Object   : oRanges = oColumn.queryContentCells( 1023 )   REM Any content.
    Dim lRow As Long
    Dim lCount As Long      : lCount  = oRanges.getCount()
dim document   as object
dim dispatcher as object
dispatcher = createUnoService("")
    If lCount > 0 Then        lRow    = oRanges.getByIndex( lCount - 1 ).RangeAddress.EndRow + 1
    Dim oCell As Object     : oCell   = oColumn.getCellByPosition( 0, lRow ) oCell )
End Sub
edit flag offensive delete link more


@Goc2 I change the above, took out My sheet name. I tested It again, it worked ok for me.

Mark McLean gravatar imageMark McLean ( 2018-03-07 12:27:15 +0200 )edit

@Mark-McLean - If you select your whole code-block, and then click the 101/010 button in the Markdown toolbar, it will format properly as code. Thanks!

David gravatar imageDavid ( 2018-03-07 12:54:25 +0200 )edit

Thank you Mark. I will apply this and evaluate the results.

Goc2 gravatar imageGoc2 ( 2018-03-07 12:54:53 +0200 )edit

Thanks @David! I wondered why it looked so odd. I'm on a dell e6420, windows 10, LO 6.0. If above needs correction or info, let me know. Again Thanks

Mark McLean gravatar imageMark McLean ( 2018-03-07 13:17:04 +0200 )edit

@mark-mclean Looks like you cracked the formatting. Takes a while to work out some of these tricks!

David gravatar imageDavid ( 2018-03-07 14:53:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-06 22:49:17 +0200

Seen: 687 times

Last updated: Apr 04 '18