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

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 11:08 AM

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

Posts: 1091

Joined: Thu Nov 17, 2005 11:08 AM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 12:43 PM

Hi,
try in this way

Code (fw): Select all Collapse

#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

Marco Boschi
info@marcoboschi.it

codemaker

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 01:37 PM

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

Posts: 1091

Joined: Thu Nov 17, 2005 11:08 AM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 02:53 PM

Boris,
try this
bye

Code (fw): Select all Collapse

#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

Marco Boschi
info@marcoboschi.it

codemaker

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 03:27 PM

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

Posts: 9022

Joined: Thu Oct 06, 2005 08:17 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 04:51 PM

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

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

codemaker

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 06:05 PM

I need a coffee and more sleep...

Gale FORd

Posts: 663

Joined: Mon Dec 05, 2005 11:22 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 09:25 PM

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

codemaker

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 09:58 PM

Gale FORd wrote: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 (fw): Select all Collapse

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

Posts: 9022

Joined: Thu Oct 06, 2005 08:17 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Tue Jul 24, 2012 10:06 PM

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

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Open XLS (or XLSX) file and save it with password
Posted: Wed Jul 25, 2012 06:58 AM

codemaker wrote:
Gale FORd wrote: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 (fw): Select all Collapse

oExcel:DisplayAlerts := .F.

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

Regards
Anser

Jeff Barnes

Posts: 933

Joined: Sun Oct 09, 2005 01:05 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Wed Jul 25, 2012 11:35 PM

This should also let you password protect the excel file.

Code (fw): Select all Collapse

oExcel:ActiveWorkbook:password=YourPassword

Thanks,

Jeff Barnes

(FWH 16.11, xHarbour 1.2.3, Bcc730)

codemaker

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Thu Jul 26, 2012 04:59 PM

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

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Thu Aug 16, 2012 05:41 PM

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

Posts: 208

Joined: Wed Dec 03, 2008 04:48 PM

Re: Open XLS (or XLSX) file and save it with password
Posted: Thu Aug 16, 2012 10:20 PM

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

FiveTech Support Forums

Continue the discussion