[Solved] Macro for Relative Rows or Columns

Creating a macro - Writing a Script - Using the API

[Solved] Macro for Relative Rows or Columns

Postby daveg7 » Wed Sep 27, 2017 5:12 pm

This is for a macro. If I'm in any column, I need to be able to move the cursor to row 40,000. Similarly, if I'm in any row, move the cursor to column Z.

I tried memorizing the keystrokes from Navigator, but I get only one absolute reference for the starting column or row. I need a macro that works from any current column or row.

(Easy to do this in Excel's VBA language.)

Thanks!
Last edited by daveg7 on Fri Sep 29, 2017 2:15 am, edited 2 times in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Macro for Relative Rows or Columns

Postby FJCC » Wed Sep 27, 2017 6:58 pm

I sounds like you are trying to record a macro, which limits what you can do pretty severely. It is easy to get a reference to a cell in row 40,000 or column Z using the API (Application Programming Interface) in OpenOffice Basic or in Python if you want to use a more modern language. Getting that reference does not "move the cursor" if you mean the visible cursor on the screen. Moving the cursor is rarely necessary in API macros. If you explain more about what you are trying to do, I or someone else can help you get started on an API macro.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6499
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro for Relative Rows or Columns

Postby daveg7 » Wed Sep 27, 2017 7:18 pm

PROJECT A:
I've got a column of text, and maybe there are some spaces, and I just want to cursor to appear for the user after the last item. I was planning to go in the same column, to a row that is far below any possible input and then simply hit Control-Up.

PROJECT B:
In any given row, I want the cursor to appear for the user, for example in Column Z, on the same row so that she can then make an entry there.

Also, is there any way to name the entire Column Z so that the macro continues to work even if I remove Column W?
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Macro for Relative Rows or Columns

Postby MrProgrammer » Wed Sep 27, 2017 8:17 pm

daveg7 wrote:Also, is there any way to name the entire Column Z so that the macro continues to work even if I remove Column W?
You cannot "remove Column W". Column W always exists. Columns A through AMJ always exist.

daveg7 wrote:In any given row, I want the cursor to appear for the user, for example in Column Z, on the same row so that she can then make an entry there.
Tools → Macros → Record
Home (moves active cell to column A of current row)
Right Arrow
Stop Recording
Give it a name, say ColZ
Tools → Macros → Organize → Basic → ColZ → Edit
Change "args2(0).Value = 1" to "args2(0).Value = 25" (25 columns to the right of column A is column Z)
Save (on toolbar) then close the macro editor

[Tutorial] Binding a macro: Shortcut key, menu or toolbar
[Tutorial] How to record a macro (and Regular Expressions)

daveg7 wrote:If I'm in any column, I need to be able to move the cursor to row 40,000.
Tools → Macros → Record
Shift+Home
Edit → Copy
⌘Home on a Mac or Ctrl+Home on other platforms
Edit → Paste
Edit → Undo
Shift+Tab
Down Arrow
Stop Recording
Give it a name, say Row40000
Tools → Macros → Organize → Basic → Row40000 → Edit
Remove "rem" in "rem dispatcher.executeDispatch(document, ".uno:Undo", "", 0, Array())"
Change "args7(0).Value = 1" to "args7(0).Value = 39999"
Save (on toolbar) then close the macro editor
Last edited by MrProgrammer on Wed Sep 27, 2017 9:25 pm, edited 4 times in total.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3324
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Macro for Relative Rows or Columns

Postby JohnSUN-Pensioner » Wed Sep 27, 2017 8:25 pm

I also did not understand what you said in the last sentence. :(

A highlight desired cells on the worksheet is not very difficult:

Code: Select all   Expand viewCollapse view
Sub GoToRow
Dim oCSelection As Variant
Dim oCell As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oSheet As Variant
Dim oColumn As Variant
Dim oEmptyRanges As Variant
   oCSelection = ThisComponent.getCurrentSelection()
Rem Selected more than one range? Take the first one
   If oCSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then _
      oCSelection = oCSelection.getByIndex(0)
   aRangeAddress = oCSelection.getRangeAddress()
   oSheet = oCSelection.getSpreadsheet()
   oColumn = oSheet.getColumns().getByIndex(aRangeAddress.StartColumn)
   oEmptyRanges = oColumn.queryEmptyCells()
   If oEmptyRanges.getCount() = 0 Then
      Print "There is no more empty cells in this column"
      Exit Sub
   EndIf
   aRangeAddress = oEmptyRanges.getByIndex(oEmptyRanges.getCount()-1).getRangeAddress()
   oCell = oSheet.getCellByPosition(aRangeAddress.StartColumn, aRangeAddress.StartRow)
   ThisComponent.getCurrentController().Select(oCell)
   ThisComponent.CurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub

Sub GoToColumn
Const COLUMN_NAME = "Z"
Dim oCSelection As Variant
Dim oCell As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim oSheet As Variant
Dim oColumn As Variant
Dim oEmptyRanges As Variant
   oCSelection = ThisComponent.getCurrentSelection()
Rem Selected more than one range? Take the first one
   If oCSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then _
      oCSelection = oCSelection.getByIndex(0)
   aRangeAddress = oCSelection.getRangeAddress()
   oSheet = oCSelection.getSpreadsheet()
   oCell = oSheet.getCellRangeByName(COLUMN_NAME & (aRangeAddress.StartRow+1))
   ThisComponent.getCurrentController().Select(oCell)
   ThisComponent.CurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 760
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests