Date: prev next · Thread: first prev next last
2022 Archives by date, by thread · List index

Have a conditional format range that is wiped out when 
spreadsheet loads and has auto import of external data.

Imports external csv file at A5 that currently covers 
A5:AV564. Had set conditional format range to 
A1:AV600, but the external link has it remove the 
formatting from A5:AV564 and just leaves the range 
above and below. Can manually go thru steps to edit 
range to fix process. Found no way to have it keep range 

Recording macro to record that process results in nothing 
being recorded, but did find that I can create a macro 
that copies format from cell A4 to A4:AV600. 
But running macro requires a number of steps, so 
assigned macro to shift-F10 as test. So, have solution that 
just requires Shift-F10.. 

sub fixcond
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("")

rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

dim args3(0) as new
args3(0).Name = "ToPoint"
args3(0).Value = "$A$4:$AV$600"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(5) as new
args4(0).Name = "Flags"
args4(0).Value = "T"
args4(1).Name = "FormulaCommand"
args4(1).Value = 0
args4(2).Name = "SkipEmptyCells"
args4(2).Value = false
args4(3).Name = "Transpose"
args4(3).Value = false
args4(4).Name = "AsLink"
args4(4).Value = false
args4(5).Name = "MoveMode"
args4(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())

dim args5(0) as new
args5(0).Name = "ToPoint"
args5(0).Value = "$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

end sub

 Michael D. Setzer II - Computer Science Instructor 
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.