[Solved] Need a macro to place numbers into specified cells

Keyboard macros or custom scripts

[Solved] Need a macro to place numbers into specified cells

Postby Eternity325 » Thu May 05, 2016 3:02 pm

It's been many many years since I have done any coding and it was only dabbling then. I need what I think will be a very easy macro to write, but I'm afraid it's still above my knowledge base! I'm looking to use VBA in OpenOffice calc to take a specified set of numbers (say 5,20, 23) and put them into specified cells (they can be named or simply be C23, doesn't matter) when I push a command button.

Anyone mind lending a hand? I've tried Google-Fu but haven't come up with anything that works thus far...
Last edited by Eternity325 on Thu May 05, 2016 5:38 pm, edited 1 time in total.
OpenOffice 3.4.1 / Windows 10
Eternity325
 
Posts: 5
Joined: Thu May 05, 2016 2:51 pm

Re: Need a macro to place numbers into specified cells

Postby RoryOF » Thu May 05, 2016 3:10 pm

Why use a macro - you'd put the numbers in by hand multiple times much quicker than you would type up a macro if you had one.
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27366
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need a macro to place numbers into specified cells

Postby Eternity325 » Thu May 05, 2016 3:17 pm

The numbers were only used as a simplified example. There will be many more than 3 when I'm done, but if I get a sample of working code I can alter it to suit my needs.
OpenOffice 3.4.1 / Windows 10
Eternity325
 
Posts: 5
Joined: Thu May 05, 2016 2:51 pm

Re: Need a macro to place numbers into specified cells

Postby RoryOF » Thu May 05, 2016 3:26 pm

Here is an article on macros that may give you some help
http://www.linuxformat.com/wiki/index.php/Automating_OpenOffice.org_-_Part_2
You should be able to find the earlier and later articles if you need them.
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27366
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need a macro to place numbers into specified cells

Postby Eternity325 » Thu May 05, 2016 3:38 pm

Thanks!
OpenOffice 3.4.1 / Windows 10
Eternity325
 
Posts: 5
Joined: Thu May 05, 2016 2:51 pm

Re: Need a macro to place numbers into specified cells

Postby Eternity325 » Thu May 05, 2016 5:29 pm

Ok so I found this code that looks like it could be modified to do exactly what I want it to do, but I keep getting errors.

sub simple_maths
dim sheet as object
dim cell as object
sheet=thisComponent.sheets.getByName("Sheet1")
cell=sheet.getCellByPosition(0,0)
cell.value=10
cell=sheet.getCellByPosition(0,1)
cell.value=10
cell=sheet.getCellByPosition(0,2)
cell.formula=”=A1+A2”
end sub

If I run this code, I get the error "Unexpected symbols" and it directs me to the quotes around ("Sheet1"), then the quotes around "=A1+A2", then finally the (redundant?) = symbol.

Upon "correcting" all of that (assuming it's a correction because I know nothing of the syntax that I should be using), the code looks as follows:

sub simple_maths
dim sheet as object
dim cell as object
sheet=thisComponent.sheets.getByName(Sheet1)
cell=sheet.getCellByPosition(0,0)
cell.value=10
cell=sheet.getCellByPosition(0,1)
cell.value=10
cell=sheet.getCellByPosition(0,2)
cell.formula=A1+A2
end sub

This code presents the error:

BASIC Runtime error
com.sun.star.container.NoSuchElementException
Message: .


I have not renamed any sheets, so Sheet 1 DOES exist. Any suggestions?
OpenOffice 3.4.1 / Windows 10
Eternity325
 
Posts: 5
Joined: Thu May 05, 2016 2:51 pm

Re: Need a macro to place numbers into specified cells

Postby RoryOF » Thu May 05, 2016 5:32 pm

Are you by any chance writing the code in Writer, then pasting it into the BASIC IDE? If so, is Writer set to change the straight quotes into curly quotes?
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27366
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need a macro to place numbers into specified cells

Postby Eternity325 » Thu May 05, 2016 5:37 pm

Wow, it was that simple! Thanks RoryOF, think I've got it covered now!
OpenOffice 3.4.1 / Windows 10
Eternity325
 
Posts: 5
Joined: Thu May 05, 2016 2:51 pm

Re: Need a macro to place numbers into specified cells

Postby RoryOF » Thu May 05, 2016 5:43 pm

Try
 Edit: new code 

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

sub simple_maths
dim sheet as object
dim cell as object
dim Doc As Object

Doc = ThisComponent
Sheet = Doc.Sheets (0)
cell=sheet.getCellByPosition(0,0)
cell.value=10
cell=sheet.getCellByPosition(0,1)
cell.value=10
cell=sheet.getCellByPosition(0,2)
cell.formula="=A1+A2"
cell=sheet.getCellByPosition(3,3)
Cell.formula="=A1+A2"
end sub
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27366
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests