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...
Camera.png
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
'______________________________________________________________________________________________________