Save As Excel Macro using Cell content

Creating a macro - Writing a Script - Using the API

Save As Excel Macro using Cell content

Postby mmoyers » 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   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
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
Last edited by acknak on Tue Jan 29, 2013 6:38 pm, edited 1 time in total.
Reason: code placed in code tags
Open Office 3.3.0 and Windows XP
mmoyers
 
Posts: 1
Joined: Tue Jan 29, 2013 6:02 pm

Re: Save As Excel Macro using Cell content

Postby B Marcelly » 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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
B Marcelly
 
Posts: 774
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Save As Excel Macro using Cell content

Postby rudolfo » 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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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())
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1415
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 4 guests