[Solved] Open worksheet in a DO loop

[rhimpel]

I hope someone out there can help me solvw a macro problem. I have
searched for examples of macros that use a do loop to open several
worksheets, say 1 to 10 and then say "your code here" and then "next".
Also, examples available are usually written for Excel or Windows..
I need the code that comes before "My code here" that actually
opens the worksheet. For example here's a macro that doesn't work:

Code: Select all

' This macro is named NewFridayDate
' My OS is Linuxmint 20.3 Mate 64 Bit
' I run Nordvpn.  I use Apache Openoffice 4.1.13

Option Explicit
sub Main

' define variables
    Dim document   as object
    Dim dispatcher as object

' get access to the document
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    
    Dim II as Integer
    Dim	Strt_Ws as Integer
    Dim Last_Ws as Integer

    Strt_Ws = 7             'starting worksheet
    Last_Ws =17             'ending worksheet
    
    MsgBox(Strt_Ws)			' check starting and ending values
    MsgBox(Last_Ws)

    for II = Strt_Ws to Last_Ws
		MsgBox(II)			' check loop values
		
        Dim args1(0) as new com.sun.star.beans.PropertyValue
        args1(0).Name = "Nr"
        args1(0).Value = II
        
        ' I cannot get past this next statement where I need to actually open
        ' the specified worksheet number.  If I can get this fixed I have
        ' more macros that I could automate and save a whole bunch of time.
		' Change anything you need to make this work.  Thanks!
        
        dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

'		My code here.

    Next

end sub 

I was hoping to attach a file here but that doesn't seem to be available. Thanks in advance.

Edit: Split from Open new file for output from Calc using macro as this is a different problem

[JeJe]

Use:

Code: Select all

sheet =thiscomponent.sheets.getbyindex(0) 'or 1 or 2 etc

Code: Select all

sheet =thiscomponent.sheets.getbyname("Sheet1")

[rhimpel]

I tried "sheet =thiscomponent.sheets.getbyindex(7) which is the worksheet I need to start with. That worked going through all pages 7-17 and the macro stopped there. I then tried "sheet =thiscomponent.sheets.getbyname("Sheet7") and it can't get past the first worksheet ending with this error: Exception: Type: com.sun.star.container. NoSuch Element Exception. Because the getbyindex(x) command works, I will continue with it. But, as the macro goes through the worksheet numbers how do I know that it is actually changing to each worksheet? I tried the following for each worksheet:
Dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$6"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
I expected the cursor to be on $A$6 on each worksheet but it wasn't. At least the do loop worked. Any suggestions?

[JeJe]

The API is a lot easier to use, so long as you know the basics. This example goes through each sheet and puts a message on each cell A6. Run it on a new document and you can see it worked on every sheet.

Code: Select all

sheets = thiscomponent.sheets 'get the sheets of the document
for i = 0 to thiscomponent.sheets.getcount -1 'go through them all
sheets.getbyindex(i).getCellRangeByName("A6").string = "Hi there" 'get the cell and add a message
next

If you explain what your code is going to be in your loop you might be able to get some help with that. The message

Exception: Type: com.sun.star.container. NoSuch Element Exception

is telling you you don't have a sheet with that name - you need to use the name of a sheet you have.
Edit: If you renamed "Sheet1" to "SheetA" for example "SheetA" is what you have to call it when using .getbyname

[rhimpel]

After trying your suggestions and succeeding I modified it to incorporate what I wanted the macro to do. I modified the date string in columns A6:A58 to show the last 52 weeks Friday dates and then copied it with Ctrl-C. I then ran the macro that starts on worksheet 6 as follows:

Code: Select all

REM  *****  BASIC  *****

' This macro is named NewFridayDate
' My OS is Linuxmint 20.3 Mate 64 Bit 
' I run Nordvpn.  I use Apache Openoffice 4.1.13
' Please read the information at the end of this macro
' Explanation at end of macro

Option Explicit

sub Main

' ----------------------------------------------------------------------
' define variables
    Dim document   as object
    Dim dispatcher as object
    Dim sheets     as string

' get access to the document
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    sheets = ThisComponent.sheets       ' get the sheets of the document

    Dim II as Integer
    Dim Strt_Ws as Integer

    Frst_Ws = 6             'worksheet counting starts at 0
'   MsgBox(Frst_Ws)

    for II = 0 to ThisComponent.sheets.getcount -1  ' go through them all 
        MsgBox(II)
'TEST   sheets.getbyindex(II).getCellRangeByName("A6").string = "Hi there"  'get the cell and add next

        Dim args4(0) as new com.sun.star.beans.PropertyValue
        args4(0).Name = "ToPoint"
        args4(0).Value = "$A$6"
        ' macro stops here:
        ' BASIC runtime error.
        ' An exception occurred
        ' Type: com.sun.start.lang.illegalArgumentException
        ' Message: sequence element is not assignable by given value!.
        dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
        dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

'        set the A column to the optimal width
        Dim args109(0) as new com.sun.star.beans.PropertyValue
        args109(0).Name = "aExtraWidth"
        args109(0).Value = 254 
        dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args109())
        dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())

        Dim args7(0) as new com.sun.star.beans.PropertyValue
        args7(0).Name = "ToPoint"
        args7(0).Value = "$A$58"
        dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

    Next

end sub

' This is an explanation of how to use this macro and what it does.  ' Every spreadsheet that has a list of Friday
' dates can use this macro ' to update the list.  The Friday date list is located in Column A, ' Rows 6:58 which has
' dates for a year, 52 Fridays.  Each week in the Friday date list must be moved up one row and row 58 will receive
' the current Friday date.  First, you must manually update the date list in spreadsheet $CA with the following
' commands after opening the spreadsheet:
'  Click on Column A, Row 7 while holding down the cursor: Slide down '   to Column A, Row 58 to hilight the row:
' Type Ctrl-C to copy the row:'   Move the cursor to Column A, Row 6 and type Ctrl-V to paste the row  to start on
' Row 6: Move the cursor to Column A, Row 58 and type the current or next Friday date: Save the spreadsheet
' Click on the next spreadsheet that follows the named daily spreadsheets if they exist, otherwise, it could be named ' "SUMMARY".  In any case, it should have a Friday date list in column A, Rows 6:58.' This is where you can run
' this macro to update all the worksheets in this spreadsheet.  A loop should open each worksheet from the current
' worksheet and update the Friday date list in Column A, Rows 6:58.  The cursor should end up on Column A, Row
' 58 and the spread sheet will be saved before moving to the next worksheet.  When the last worksheet has been
' updated the macro will end.

The added part was created by Tools>Record Macro. I'm just trying to keep it simple but it doesn't work inside the loop. That is my main problem. If I had my druthers, I'd start this macro to in the first worksheet (0), make the changes, then execute the do loop, and simply copy from worksheet(0) columnA6:A58 to all the other worksheets. My goal is to stop having to select by clicking on Tools>Macros>Run Macros>Standard>NewFridayDate for each worksheet. I have another spreadsheet with 170 worksheets so you can see why this is important to me. By the way, I used to program in FORTRAN but after retiring is use PERL for everything now. Anyway that's where I'm at now. I really appreciate your help and your knowlege and I hope I'm not taking up much of your time. Thanks.

[JeJe]

If I had my druthers, I'd start this macro to in the first worksheet (0), make the changes, then execute the do loop, and simply copy from worksheet(0) columnA6:A58 to all the other worksheets.

This should copy across what's in range "A6:A58" on the first sheet to all the other sheets.

Code: Select all

sheet=thiscomponent.sheets(0) 'get sheet
controller =thiscomponent.currentcontroller 'get the current controller
controller.select sheet.getCellRangeByName("A6:A58") 'select the required range on the controller
trans = controller.gettransferable 'copy contents (does not use the clipboard)
wait 100 'may not need this

for i = 1 to thiscomponent.sheets.count -1
controller.select thiscomponent.sheets.getbyindex(i).getCellRangeByName("A6:A58") 'select the same range on all the other sheets
wait 100 ' give it some time to do the selecting
controller.inserttransferable trans 'insert the copied contents
next