Ask Your Question
0

How to cylce through selected spreadsheets?

asked 2015-12-21 12:08:33 +0100

Ulli_Wue gravatar image

updated 2016-03-09 21:09:05 +0100

Alex Kemp gravatar image

I'm trying to cycle through spredsheets using basic. I found the code-snippet below. But how can I cycle through those sheets which are alread selected (e.g. multiple selection, but not those which are unselected)? I found the object SelectedSheetsEnum, but how can I use it? Any ideas?

Best wishes

Ulli

sheets = Thiscomponent.getSheets.createEnumeration 
While sheets.hasMoreElements
    oSheet = sheets.nextElement()
    MsgBox "Next sheet name is " & oSheet.getName
Wend
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-12-21 20:06:41 +0100

JohnSUN gravatar image

updated 2015-12-21 20:23:40 +0100

I think that you meant something like the following code

Sub cycleBySelectedSheets
Dim oCurrentSelection As Variant
Dim fullRangeAddress As String
Dim tmpArray As Variant
Dim oSheets As Variant
Dim oSheet As Variant
Dim i&
    oCurrentSelection = ThisComponent.getCurrentSelection()
    fullRangeAddress = oCurrentSelection.AbsoluteName
    fullRangeAddress = Join(Split(fullRangeAddress,"$"),"")
    oElementNames = Split(fullRangeAddress,";")
    oSheets = ThisComponent.getSheets()
    For i = LBound(oElementNames) To UBound(oElementNames)
        tmpArray = Split(oElementNames(i),".")
        oSheet =  oSheets.getByName(tmpArray(0))
REM Do something with this sheet... For example:
        Print "Processing " + oSheet.getName()
    Next i  
End Sub

But if you mean "select some sheets, with Ctrl key select some ranges on them and then work with each of this selections" so

Sub cycleBySelectedRanges
Dim oCurrentSelection As Variant
Dim oSheets As Variant
Dim oRanges As Variant
Dim oRange As Variant
Dim i&
    oCurrentSelection = ThisComponent.getCurrentSelection()
    oSheets = ThisComponent.getSheets()
    oRanges = oSheets.getCellRangesByName(oCurrentSelection.AbsoluteName)
    For i = LBound(oRanges) To UBound(oRanges)
        oRange = oRanges(i)
REM Do something with this range...
        Print "Processing " + oRange.AbsoluteName
    Next i  
End Sub
edit flag offensive delete link more
0

answered 2015-12-22 15:16:08 +0100

Ulli_Wue gravatar image

This is perfect! Works great. Thank you very much. Big respect and best wishes. Ulli

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-12-21 10:32:30 +0100

Seen: 80 times

Last updated: Dec 21 '15