Save As Excel Macro using Cell content

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
mmoyers
Posts: 1
Joined: Tue Jan 29, 2013 6:02 pm

Save As Excel Macro using Cell content

Post 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

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
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Save As Excel Macro using Cell content

Post by B Marcelly »

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.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Save As Excel Macro using Cell content

Post 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

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())
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.
Post Reply