[Nothing to Solve] Excel and Calc, One Complaint Each

Discuss the spreadsheet application

[Nothing to Solve] Excel and Calc, One Complaint Each

Postby daveg7 » Mon Oct 02, 2017 4:04 am

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.
Last edited by daveg7 on Mon Oct 02, 2017 5:54 am, edited 1 time in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: Excel and Calc, One Complaint Each

Postby RusselB » Mon Oct 02, 2017 4:41 am

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,
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5683
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Excel and Calc, One Complaint Each

Postby daveg7 » Mon Oct 02, 2017 5:53 am

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.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: [Nothing to Solve] Excel and Calc, One Complaint Each

Postby RusselB » Mon Oct 02, 2017 6:00 am

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5683
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Nothing to Solve] Excel and Calc, One Complaint Each

Postby daveg7 » Mon Oct 02, 2017 6:07 am

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
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
daveg7
 
Posts: 48
Joined: Wed Sep 27, 2017 5:01 pm

Re: [Nothing to Solve] Excel and Calc, One Complaint Each

Postby Zizi64 » Mon Oct 02, 2017 11:36 pm

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   Expand viewCollapse view
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
'______________________________________________________________________________________________________
Attachments
CameraDisplay.ots
(7.61 KiB) Downloaded 57 times
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8538
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests