Page 1 of 1
[Nothing to Solve] Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 4:04 am
by daveg7
In Excel 2003, after a VBA macro, I can't use Undo for the results (though some people claim to have invented complicated ways to achieve this). In Calc, I've had Undo work every time I've tried it after a macro.
On the other hand, one of my favorite and most useful tools in Excel, the Camera, doesn't seem to have an analogue in Calc. I love to use Camera to make a live picture of part of the spreadsheet and then place the graphic, in any size, where it's convenient for me.
Call it my wishful thinking for both applications.
Re: Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 4:41 am
by RusselB
In case you weren't aware of it, this forum is user based. There are few, if any, developers that monitor this forum.
There is a
development mailing list
As to your problems with Excel 2003, a Microsoft forum might have answers for you.
Not being familiar with the Camera tool in Excel, can you describe what it does/how it works? There might be a work-around to get you something similar,
Re: Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 5:53 am
by daveg7
I was just sharing my thoughts about the two applications, not wanting to achieve anything specific.
In Excel, I can highlight some cells and click on the Camera tool, which puts an image of the region into memory. Then I paste the image anywhere else in the Workbook (which means that it can be in a different sheet).
The main point is that the image is live; it's like having a porthole into another room, or rooms, so that the user can watch things changing. Also, you can resize the image and put it anywhere; it's a window within your sheet. You can even put the image into one large cell, if you want to, and it helps avoid many formatting issues.
Imagine not just subtotals from everywhere, in compressed form, but also tiny graphs.
I've made big use of this feature, and many times. I think it's amazing, and for me it would be sufficient reason to choose one application over another.
Re: [Nothing to Solve] Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 6:00 am
by RusselB
Not being a Mac user, I don't know if there's a similar program or not, but in my version of Windows there is a tool called Snipping Tool, which sounds to me, as something very similar to the Camera tool you are talking about.
Re: [Nothing to Solve] Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 6:07 am
by daveg7
Hah! I think you missed the most important part of my explanation: the image captured by the Excel Camera Tool is LIVE, and it changes instantly when there are recalculations.
Not a Mac issue. The Camera Tool is part of Excel, whether in the Mac or in the PC.
I use Snipping Tool all the time. In comparison to Camera, Snipping is just a very nice toy that gives me a photo frozen in time. It just sits there looking pretty but doing nothing new.
With respect,
David
Re: [Nothing to Solve] Excel and Calc, One Complaint Each
Posted: Mon Oct 02, 2017 11:36 pm
by Zizi64
A workaround tip:
Create a new Calc document, resize the window, hide all of menus toolbars, sliders and other control features, link a selected cellrangy from the source document.
You can do these steps by a macro. My example macro works in the LibreOffice. The LO 5.3.6 has a useful feature: you can customize the local context menus, therefore you can add some control functionality of the control-less "Camera" panels/windows: Zoom, Refresh, etc...
Copy the macro code into the MyMacros - Standard Directory - Camera module (create it before), and then assign the macro to some shortcut key, or a toolbar icon, or a menu item, or a form control element.
Copy the attached CameraDisplay.ots template file into the Backup directory of the LibreOffice. That file is an empty .ots file, but i have assigned the refreshing macr oto the event "Activate document" , and added some plus functionality to the context menu of the document: the refresh macro (from the Module "Camera" of the Standard library of the MyMacros), and the Zoom Extent and the Save as built-in functions. These will not work in the Apache OpenOffice.
Code: Select all
REM ***** BASIC *****
Option explicit
Sub MyCameraDisplay()
Dim oDoc, Dummy() as object
Dim oSourceDoc as object
Dim oFrame as object
Dim dispatcher as object
Dim args2(0) as new com.sun.star.beans.PropertyValue
Dim args3(0) as new com.sun.star.beans.PropertyValue
Dim args5(0) as new com.sun.star.beans.PropertyValue
Dim oLmgr As Object
Dim oWindow As object
Dim oRect As New com.sun.star.awt.Rectangle
Dim sCamDisp_URL as string
Dim CamPosX as long
Dim CamPosY as long
Dim CamWidth as long
Dim CamHeight as long
oSourceDoc = ThisComponent.CurrentController.Frame
oRect = oSourceDoc.getContainerWindow().getPosSize()
CamWidth = 400
CamHeight = 300
CamPosX = oRect.Width - CamWidth
CamPosY = oRect.Height - CamHeight
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(oSourceDoc, ".uno:Copy", "", 0, Array())
sCamDisp_URL = CreateUnoService("com.sun.star.util.PathSettings").Backup & "/" & "CameraDisplay.ots"
If FileExists(sCamDisp_URL) then
oDoc = StarDesktop.loadComponentFromURL (sCamDisp_URL, "_blank", 0, Dummy())
Else
oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_Blank", 0, Dummy())
end if
oFrame = oDoc.CurrentController.Frame
oLmgr = oFrame.LayoutManager
oLmgr.setVisible(false)
oDoc.CurrentController.VerticalScrollBar = false
oDoc.CurrentController.HorizontalScrollBar = false
oDoc.CurrentController.ColumnRowHeaders = false
oDoc.CurrentController.SheetTabs = false
oWindow = oFrame.getContainerWindow()
oRect = oWindow.getPosSize()
oWindow.setPosSize( CamPosX, CamPosY, CamWidth, CamHeight, com.sun.star.awt.PosSize.POSSIZE )
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1"
dispatcher.executeDispatch(oFrame, ".uno:GoToCell", "", 0, args2())
args3(0).Name = "SelectedFormat"
args3(0).Value = 59
dispatcher.executeDispatch(oFrame, ".uno:ClipboardFormatItems", "", 0, args3())
dispatcher.executeDispatch(oFrame, ".uno:ZoomOptimal", "", 0, Array())
oDoc.CurrentController.Frame.SetTitle("Camera_" & Format(Year(Now), "0000") & Format(Month(Now), "00") & Format(Day(Now), "00") & "_" & _
Format(Hour(Now), "00") & Format(Minute(Now), "00") & Format(Second(Now), "00")
End Sub
'______________________________________________________________________________________________________
sub UpdateLinks
ThisComponent.calculateAll()
end sub
'______________________________________________________________________________________________________