Find first empty row

[Goc2]

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("com.sun.star.frame.DispatchHelper") 
oFrame1 = oDoc1.CurrentController.Frame 
oSheet = oDoc1.Sheets(0) 
rng = oSheet.getCellRangeByName("AP25:AR25") 
oDoc1.CurrentController.Select(rng) 
oDispatcher.executeDispatch(oFrame1, ".uno:Copy", "", 0, Array()) 
rng = oDoc2.Sheets(0).getCellRangeByName("A1:A3") 
oDoc2.CurrentController.Select(rng) 
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("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
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 com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 10

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
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 com.sun.star.beans.PropertyValue
args6(0).Name = "Nr"
args6(0).Value = 3

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

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$AM$26"

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

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

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Nr"
args9(0).Value = 10

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

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$F$5"

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

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

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Nr"
args12(0).Value = 3

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

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$AM$27"

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

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

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "Nr"
args15(0).Value = 10

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

rem ----------------------------------------------------------------------
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = "ToPoint"
args16(0).Value = "$G$5"

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

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


end sub

[Lupp]

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 com.sun.star.sheet.SpreadsheetDocument 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"
doc2=StarDesktop.LoadComponentFromURL(ConvertToURL(fN),"_blank",0,Array())

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

[MarkMcLean]

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. https://goo.gl/aLkBSe Again. THANK

[Goc2]

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

[Goc2]

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

[MarkMcLean]

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. ??

[Goc2]

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]

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.

Now:

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

[Lupp]

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

[Goc2]

I Did. Duh…

Now:

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

[Lupp]

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.

[Goc2]

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?

[Lupp]

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.

[Goc2]

Lupp, Didn’t mean to puzzle you. Just wanted to make sure I was understanding before spending even more time trying to get this to work. What your reaffirming has done for me is I know it works if you ran it with success. I will continue to look at things to see if I can breath life into it.

[Goc2]

Moved the file out of the root into a folder. Now it’s calling out an unsupported URL. I triple checked the path and the path it’s calling out (below) in the error message appears to be a proper path for Windows

`Message: Unsupported URL <file:///c:/Users/computer%20one/My%20Documents/Summary.ods>: "type detection failed".`

The space encoding %20 shouldn’t be a problem should it?

Also used a blank/fresh Summary.ods spreadsheet being copy/pasted to from the main.ods spreadsheet.

[Lupp]

I cannot reproduce anyone of the errors. The first time in my life I even created a folder with one of those silly names containing a space. No chance: No error.
This is getting really annoying. I have to leave the thread.

[Goc2]

Lupp, Thank you for your time.

Sorry you became annoyed, that was not the intent. I came to this forum to resolve a legitimate problem.
As for windows putting spaces in a path I don’t know why they do such silly things. It’s Windows. Again, I asked about the spaces to be sure no stone was being left unturned; no other reason.

If anyone else could jump in to provide insight on something that might be causing this I would appreciate it. Thank you.

[Lupp]

Sorry. I’m not a native speaker of English and may have used a term in the wrong way. The “annoying” didn’t aim at you but at the strange situation I couldn’t manage. How to handle errors I never saw with my own eyes? Concerning spaces in pathnames: Yes they are coded the html way. I’m on Win, too, and have to accept a few paths containing spaces, in specific the extruciating ‘program files’. I never give a name of the kind to one of my files and Win doesn’t force me.

[Lupp]

Your questions are legitimate, of course. However, the current discussion is very far off the original question of this thread. To get new attention for your current problems with opening secondary spreadsheet documents and with using ranges in these documents, you should open a new thread and post the few relevant lines of code.
I personally never had a similar problem under Win 10 with many different versions of LibO and AOO. Did you read Pitonyak meanwhile?

[Goc2]

Lupp. Thank your for your reply.

Actually I have been reading the Pitonyak pubs. This approach actually came from “OpenOffice.org Macros Explained” page 497. This is not something I’ve attempted in 5 minutes of my time and immediately went to the forum. I have been digging for weeks looking for the content I need to accomplish this. I came to the forum after doing much due diligence hoping someone could help.

Ran out of characters and will post another comment to continue.