Apache OpenOffice Community Forum

Posted: **Tue Jan 29, 2013 6:32 pm**

I have the following macro that saves the document using contents of a cell (C1), prints two copies, and then e-mails me the spreadsheet. I am trying to change the macro to save the file as an Excel file (.xls) instead of a Calc file.
I have found macros to save using file content like this and macros that will "Save As" but I haven't been able to combine the two. My guess is that I just need to add the Save As function with 'Filter' somewhere but I am at a loss.

I also want to pull the e-mail address from a cell also.

Any suggestions?

Code: Select all

sub print_email


oDoc =ThisComponent 
oSheet = oDoc.Sheets(0) 
c = oSheet.getCellRangeByName("G4").String

 
sPath ="C:\Documents and Settings\My Documents\Returns\" 
rem if not fileexists(sPath) then 
rem  msgbox "Path does not exist" 
rem endif 
oDoc =ThisComponent 
oSheet = oDoc.Sheets(0) 
a = oSheet.getCellRangeByName("C1").String 
b = CDatetoISO(NOW)
sFile= sPath & a & " " & Format( Now(), "YYYY"+"-"+"MM"+"-"+"DD"+"_"+"HH"+"-"+"MM"+"-"+"SS" )+".ods" 
Url = ConvertToUrl(sFile) 
If FileExists(Url) then 
 iAns = MsgBox("Click 'OK' to overwrite the existing file.",1,"   !!! FILE EXISTS !!!" 
 If iAns <> 1 then End 
else 
msgbox "going to save"& sFile 
EndIf 
oDoc.StoreAsUrl(Url,Array()) 



rem begin working macro
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PrintDefault", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:PrintDefault", "", 0, Array())
 
   Dim aAttachement(0) As String, aCCRecipients(0) As String 
   sRecipient = "myemail@mycompany.com" 
   sSubject = "Rx Return Sheet" 
   aAttachement(0) = ConvertToURL(ThisComponent.URL) 
   nFlag = 1   '0-opens compose window,  1-sends mail immediately 
   Call subSendMail(sRecipient,sSubject,aAttachement(),nFlag) 
   
End Sub 

Sub subSendMail(sRecipient As String,sSubject As String,aAttachements,nFlag As Integer) 
' nFlag:  '0-opens compose window,  1-sends mail immediately 
   oMailer = createUnoService( "com.sun.star.system.SimpleSystemMail" ) 
   oMailClient = oMailer.querySimpleMailClient()  
   oMessage = oMailClient.createSimpleMailMessage() 
   oMessage.setRecipient( sRecipient ) 
   oMessage.setSubject( sSubject ) 
   oMessage.setAttachement( aAttachements() ) 
   oMailClient.sendSimpleMailMessage(oMessage,nFlag) 
End Sub

Posted: **Wed Jan 30, 2013 9:41 am**

Hi,

I am trying to change the macro to save the file as an Excel file (.xls) instead of a Calc file.

It can't work since OpenOffice macros are incompatible with Excel file format. The document macros would be deleted.

Posted: **Thu Jan 31, 2013 3:47 am**

As Bernard Marcelly has pointed out: A OpenOffice macro won't survive if you store it in a Spreadsheet file in .xls (Excel 97/2000) format.
But it might be a good idea to store the macro in your Profile and not in the file. If it is in the Standard section, it is automatically loaded each time you fire up OpenOffice and it is then available for all files that you open in OpenOffice. Your guess with SaveAs and filter is right.

Code: Select all

oDoc.StoreAsUrl(Url,Array())

is the right UNO API to use for this purpose. But the second parameter can't be empty in your case when you save to Excel .xls. But this is one of the rare cases where the macro recorder is useful.

Activate the macro recorder
Save a .ods file as .xls file
Stop the macro recorder

Save the macro and inspect it. The structure will look like:

Code: Select all

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Users/rolf/Datbase.pdf"
args1(1).Name = "FilterName"
args1(1).Value = "writer_pdf_Export"
args1(2).Name = "FilterData"
args1(2).Value = Array( .... )

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args1())

The crucial point is that although the macro recorder uses dispatcher commands like "uno:ExportTo" it still produces a Property-Value array that can also be passed to .storeToURL as parameter array. Of course the number of elements has to be reduced by one Name:Value pair, becaue the URL parameter is given separately as first parameter to .storeToUrl:

Code: Select all

dim args(1) as new com.sun.star.beans.PropertyValue
args(0).Name = "FilterName"
args(0).Value = ' ... use what the macro recorder has recorded for saving as Excel
args(1).Name = "FilterData"
args(1).Value = ' ...again take the FilterData value from the macro recorder

doc.storeToURL(url,args())

Apache OpenOffice Community Forum

Save As Excel Macro using Cell content

Save As Excel Macro using Cell content

Re: Save As Excel Macro using Cell content

Re: Save As Excel Macro using Cell content