Open XLS (or XLSX) file and save it with password

[codemaker]

I create some XLS files from porgram if I want to export the report into XLS. This works fine.
Then I save the file and finish with some XLS file, for example "boris.xls" which I email to the client automatically
I use TFILEXLS class to create XLS files from code

Now, some of my clients wants me to protect the resulting XLS with password and email them as protected XLS.
TFILEXLS class doesn't contain any such function.
I was thinking to load the resulting XLS using some other technique (CreateObject() ot TOLE ) and immediately save it under the same name, this time with password.
But, never worked with CreateObject() or OLE

Can somebody help me with this please?
Boris

[MarcoBoschi]

Hi,
try in this way

Code: Select all  Expand view

#include "fivewin.ch"

FUNCTION MAIN( )
LOCAL oExcel
LOCAL oFoglio
LOCAL oAS

oExcel := CreateObject("Excel.Application")
oFoglio := oExcel:WorkBooks:Add

oAs := oExcel:Activesheet()

oAs:Cells( 1 ,  1 ):Value := "123456"

oFoglio:SaveAs( "c:\fwh\samples\codmaker.xls" , [b]43[/b] , "123456" )

oExcel:Quit()
RETURN NIL

#DEFINE xlExcel5 39
#DEFINE xlExcel7 39
#DEFINE xlExcel9795 43

[codemaker]

When I try to save, I got this error.

Error description: Error Excel.Application:WORKBOOKS/14 DISP_E_BADPARAMCOUNT: SAVEAS
Args:
[ 1] = C C:\Myguests\akcije.xls
[ 2] = N 43
[ 3] = C i1978

The line where the program crashes is this
oFoglio:SaveAs( fileXL , 43 , "i1978" )

I am not sure what is this parameter: 43
I cannot compile it if I leave it as that. If I put only number 43, it compiles, but I get the above error...

Also, I don't create the XLS, I want to LOAD an existing one and then save it immediately
Is there some specific .CH file needed here?


Can you please show me:
1. how to only LOAD the existing XLS. I do it this way:
fileXL := " C:\Myguests\akcije.xls"
oExcel := CreateObject("Excel.Application")
oFoglio := oExcel:WorkBooks
oFoglio:Open( fileXL )

2. How to save it under the same name, just with password this time

Thank you for your time
Boris

[MarcoBoschi]

Boris,
try this
bye

Code: Select all  Expand view

#include "fivewin.ch"

FUNCTION MAIN( cFileIn , cFileOut )
LOCAL oExcel
LOCAL oFoglio

oExcel := CreateObject("Excel.Application")
oFoglio := oExcel:WorkBooks:Open( cFileIn, OleDefaultArg() , OleDefaultArg() , OleDefaultArg() )

oFoglio:SaveAs( cFileOut , OleDefaultArg() , "123456" )

oExcel:Quit()

RETURN NIL

[codemaker]

Thanks Marco,
this is exactly what I need!

Is there any way to "silently" overwrite the existing file?
If I load file A.XLS and want to save it back to A.XLS - the program issues the message asking me if I want to overwrite the existing file.
I would like to avoid this message and save the file without question.
Any idea?

In any case thank you so much for your help. I think I will have to study this CreateObject() function

Boris

BTW: Somewhere in October I will be visiting Milano, my wife is already pushing me to stop in Verona also. Maybe I can stop in Padova to meet you, maybe we can drink a coffee together
Thanks again

[Enrico Maria Giordano]

Just FErase() the old file before saving the new.

EMG

[codemaker]

I need a coffee and more sleep...

[Gale FORd]

You can ferase() the file as long as you did not open it already in Excel.

[codemaker]

You can ferase() the file as long as you did not open it already in Excel.

Well, I probably need even more coffee...
Since I load the XLS into Excel, I cannot FERASE it before saving back to the same name.

So I am back to square one. How can I avoid the program asking me if I want to overwrite the existing XLS.... ?

Code: Select all  Expand view

oExcel := CreateObject("Excel.Application")
oFoglio := oExcel:WorkBooks:Open( FileXL, OleDefaultArg() , OleDefaultArg() , OleDefaultArg() )

FERASE(FileXL)           // PROBLEM!!!

oFoglio:SaveAs( FileXL , OleDefaultArg() , "i1978" )
oExcel:Quit()
 

[Enrico Maria Giordano]

No, you are saving the file with a new name so you can FErase() the new name if it already exists.

EMG

[anserkk]

So I am back to square one. How can I avoid the program asking me if I want to overwrite the existing XLS.... ?

Try this and let us know

Code: Select all  Expand view

oExcel:DisplayAlerts := .F.

By assigning .F. to the property DisplayAlerts, Excel will execute the method without waiting for our confirmation.

Regards
Anser

[Jeff Barnes]

This should also let you password protect the excel file.

Code: Select all  Expand view

oExcel:ActiveWorkbook:password=YourPassword

[codemaker]

Probably what you suggest will work either... but I used what "anserkk" suggested. and works

Now I simply load XLS,

then I use
oExcel:DisplayAlerts := .F.

Then I save the XLS back using a password parameter. The program doesn't complain about asking to overwrite, it just silently overwrite the existing XLS, this time with password

Problem solved
Thanks to all of you

Boris

[codemaker]

Unfortunately problem is not solved completely.

Before I added a password into XLS, all of my clients were able to open the XLS created by our program.

After I added the password to the XLS using two lines of code, the clients which uses "2002 Excel" cannot open the password protected file.
If I open the created XLS and save it as "Excel 2003 - 97" file, the client with "2002 Excel" can open this file.

I am using "Excel 2007" compatible functions to create XLS and seems that the difference between 2002 and 2007 (regarding passwords) is too big.

I can solve the problem and offer the option to the user of my program, to define NOT to embed the password into XLS, then the client with 2002 Excel can open it.
But this client also wants to have password protected XLS, I cannot do it.

Is there some way I can insert the password in a way that "2002 Excel" (or any other version) can "understand" this?

[codemaker]

In had my clients which cannot open the password protected XLS, download the Open Office and now they can open the XLS, no mater whic Office version our program uses to create XLS and password protect it
Case closed