Gratefully building on posts I found here on how to hide a series of columns, I am finding that the macros I put together from them works, but take a fair amount of time.
Probably because the way I wrote the code is not terribly efficient, or maybe the function I am using is not very fast.
I suspect there's a better way.
If someone has input, I would welcome it. Code is below.
The first one keeps the first 7 columns of the spreadsheet (starting at A1) visible, then hides the next 42, then shows the next 7, then hides the next 42, etc, until some number of columns that I have hard-coded into the subroutine.
The second one keeps the first 7 columns visible (starting at the column that contains the selected cell), and then hides the next 42 columns, and that's it.
Although I'm not sure the "why" is important in speeding up the subroutines, in case it is, here's why I want to do this: I have a spreadsheet that I use to tally information for each day of the week. Each day of the week is 7 columns wide. In the first sub, I want to be able to hide everything but the first day of the week for all the weeks in the spreadsheet (I usually do about 6-8 weeks at a time). In the second sub, I want to hide everything but the first day of the current week (so, hiding the other days of just a single week), where the selected cell is on the first column of the first day of the week that I want to hide.
I hope that makes sense.
Many thanks to anyone who has a suggestion of how I might do this faster.
- Ellen
--------------------------------
Code: Select all
sub hide_all_weeks
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 set the properties for moving right
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
rem make thecurrent column counter
dim cCol as integer
cCol = 1
mCol = cCol
rem Ellen's hack so you don't keep going forever, but for just nCol columns
nCol = 500
rem goto the first column
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem loop until you get to the max
Do Until cCol > nCol
if mCol > 6 then
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
End if
rem goto the right nad increment the column counter
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
cCol = cCol + 1
rem show the first 7 columns, then hide the next 42 columns, then show 7 columns, then hide the next 42, etc
mCol = cCol mod 49
Loop
End sub
sub hide_one_week
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 get the current column - for this to work, have to select a cell in the first column of that week's Monday
currCol = ThisComponent.CurrentSelection.CellAddress.Column
rem set the properties for moving right
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
rem make thecurrent column counter
dim cCol as integer
cCol = 1
mCol = cCol
rem Stop when reach the end of the week
nCol = 49
rem goto the first column
rem dim args1(0) as new com.sun.star.beans.PropertyValue
rem args1(0).Name = "ToPoint"
rem args1(0).Value = "$A$1"
rem I commented out the line below bc I *don't* want to go to the first column. I want to start where we are.
rem dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem loop until you get back to the selected cell
Do Until cCol > nCol
rem hide if the cell value is 0
rem if ThisComponent.CurrentSelection.string <> "" and ThisComponent.CurrentSelection.value = 0 then
if mCol > 6 then
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
End if
rem goto the right nad increment the column counter
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
cCol = cCol + 1
mCol = cCol mod 49
Loop
End sub
