Ask Your Question
0

Macro to Export as Graphic in Calc [closed]

asked 2013-08-16 02:32:35 +0200

bfris gravatar image

updated 2015-11-07 03:54:56 +0200

Alex Kemp gravatar image

I'm wondering if there is a StarBasic interface to "Export as Graphic" yet.

LibO 4.1 Calc has this great feature that allows you to save a graph to a file in many formats, including vector formats. Playing around with SVM (StarOffice metafile) looked really nice when inserting into writer.

I have roughly a hundred graphs in Excel that I now wish to export using Calc. And I don't want to do it by hand. I've tried the macro recorder (intercepting dispatches), but the recorded macro only has a REM'd out command with little clues on how I might use it.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-07 03:55:13.847426

1 Answer

Sort by » oldest newest most voted
0

answered 2013-08-20 23:49:49 +0200

bfris gravatar image

updated 2013-08-21 01:29:43 +0200

The only way I have figured out how to do this is using keystroke macros. This will only work in Microsoft Windows. I'm not proud of this solution. But it sort of works. There were two major obstacles in accomplishing this using API or Dispatch calls:

  1. I haven't found how to select a chart as a chart. All of the examples I've seen select the chart as a shape. In this mode, the Export as Graphic command is not available
  2. It is possible to get the Export as Graphic dialog to pop up on a selected chart (if you select it manually) by using the .uno:ExportAsGraphic dispatch, but I couldn't figure out how to fill in the file name and file type automatically.

This technique uses some BASIC code and a separate VBScript script. The BASIC code must be called from Calc and not in the IDE. Here is the basic code:

Sub ExportGraphic(SheetIndex as integer, fname as string)
    ' this macro will only work on Microsoft Windows
    ' it saves the graph on Worksheet <SheetIndex> to the file name 
    ' <fname>
    '
    '
    ' this macro must be run from the Calc spreadsheet. do not run 
    ' from the BASIC IDE or keystrokes will be sent to the IDE instead
    ' of Calc.
    ' 
    ' we achieve this end by really cheesy keystroke macros. this is 
    ' not a good way to things. but there is no other way to call the 
    ' Export as Graphic function newly introduced in LibO 4.0 and 
    ' improved in Lib0 4.1

    ' currently this saves as SVM file. to use a different file type, you'll
    ' have to edit the companion vbs file to change the number of UP arrows pressed


    ' first select the chart. this actually selects the chart by shape. if 
    ' we could select the chart any other way, we might be able to this with 
    ' fewer keystrokes sent
    oShape = ThisComponent.getDrawPages().getByIndex(SheetIndex).getByIndex(0)
    oController = ThisComponent.getCurrentController()
    oController.select(oShape)
    Wait 2000

    ' if we were to right click on the chart now, the right click menu would not 
    ' contain the  Export as Graphics command we want. So we right click, send some
    ' up and down arrow key strokes followed by a TAB
    CreateObject("WScript.Shell").SendKeys("+{F10}{DOWN 3}{ESC}")  'Shift+F10 right click
    Wait 1000
    CreateObject("WScript.Shell").SendKeys("{TAB}") 
    Wait 1000

    ' At this point the chart should be selected as a chart ...
(more)
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-08-16 02:32:35 +0200

Seen: 477 times

Last updated: Aug 21 '13