Page 1 of 1

Save As Excel Macro using Cell content

PostPosted: Tue Jan 29, 2013 6:32 pm
by mmoyers
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   Expand viewCollapse view
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
msgbox "going to save"& sFile

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("")

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 = ""
   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( "" )
   oMailClient = oMailer.querySimpleMailClient() 
   oMessage = oMailClient.createSimpleMailMessage()
   oMessage.setRecipient( sRecipient )
   oMessage.setSubject( sSubject )
   oMessage.setAttachement( aAttachements() )
End Sub

Re: Save As Excel Macro using Cell content

PostPosted: Wed Jan 30, 2013 9:41 am
by B Marcelly
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.

Re: Save As Excel Macro using Cell content

PostPosted: Thu Jan 31, 2013 3:47 am
by rudolfo
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   Expand viewCollapse view

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   Expand viewCollapse view
dispatcher = createUnoService("")
rem ----------------------------------------------------------------------
dim args1(2) as new
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   Expand viewCollapse view
dim args(1) as new
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