Macro to Export as Graphic in Calc

[bfris]

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.

[bfris]

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 and if right clicked,
	' the Export as Graphics command will be there. But there is more trickery to be
	' performed. We have to call an external Visual Basic script that "types" the file
	' name and selects the file type to save as. If we were to send a right click 
	' from LibO, the macro would get stuck waiting for the Export as Graphic dialog to
	' close before resuming execution.	
	CreateObject("WScript.Shell").Run("cscript z:\code\vbscript\right_click_alt_h.vbs " & fname, False)  'Shift+F10 right click
    Wait 10000

End Sub

and here is the right_click_alt_h.vbs code:

'==========================================================================
'
'
' NAME: right click alt h.vbs
'
' COMMENT: sends right click and alt h keystrokes
'
'==========================================================================
'
'ENTER  {ENTER} or ~
'TAB  {TAB}
'SHIFT  +
'CTRL   ^
'ALT  %

'create a shell object:
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

fname = WScript.Arguments(0)

WshShell.AppActivate("LibreOffice Calc")

delay = 500

WshShell.SendKeys ("+{F10}")
WScript.Sleep(5000)
WshShell.SendKeys ("%h")
WScript.Sleep(delay)
WshShell.SendKeys (fname)
WScript.Sleep(delay)
WshShell.SendKeys ("{TAB}")
WScript.Sleep(delay)
WshShell.SendKeys ("{DOWN}{UP 4}")
WScript.Sleep(delay)
WshShell.SendKeys ("{ENTER 2}")
WScript.Sleep(delay)


Set WshShell = Nothing