Open Read/Write Password Protected Excel Document

[Dominic_Fichera]

Hi all,

I am wanting to implement a feature that reads and writes to excel... But have the excel document be password protected.

Currently my script reads and writes like a dream (hard to mess that up ) but I wanted to know if this was possible using the internal MS commands.

Thanks heaps!

Dominic Fichera

[JRL]

Create a separate script dynamically that waits for the password window to pop up then sends the password. After the excel file opens the original script deletes the dynamic script. The caveate is that should something interrupt the process and the dynamic script is left behind, it will contain the password for the excel file. I think this is a small risk but I don't know your tolerance for risk.

Hope this helps.
Dick

Code: Select all

Let>Password=pass
Let>ExcelFile=%userdocuments_dir%\MyExcelFile,xlsx
Let>OpeningScript=%temp_dir%UnlockMyExcel.scp

LabelToVar>Unlock,vScriptData
WriteLn>OpeningScript,wres,vScriptData

ExecuteFile>OpeningScript


XLOpen>ExcelFile,0,xlhandle
DeleteFile>OpeningScript

//The rest of your script

/*
Unlock:
WaitWindowOpen>Password
Wait>0.3
Send>%Password%
Wait>0.3
Press Enter
*/

[CyberCitizen]

You true about it being left behind, however you could use the crypt command to hide the contents as well, just to make it a little harder to read.

[JRL]

...you could use the crypt command to hide the contents as well...

Not sure I follow you on this but I'm not very familiar with using encryption. If you wrote the script out encrypted it would make it difficult to decipher on the off chance it got left behind, but how would one execute the encrypted script?

[CyberCitizen]

You can store the encrypted value in the script, so it will show as random letters etc instead of password. MS knows how to handle the information, but you won't see a plain text password if the script was left behind, if someone knew you were using MS, they could copy the data to MS & step though it to work it out, but its more about reducing the risk in the off chance that the file was to be left behind.

[JRL]

Right. I see now. I thought you meant the entire script would be encrypted. So if the password is passed to the dynamic script encrypted and then decrypted in the script, the password never displays in the script file. Something like this.

Code: Select all

Let>Password=pass
Crypt>mouse,Password,Password
Let>ExcelFile=%userdocuments_dir%\MyExcelFile.xlsx
Let>OpeningScript=%temp_dir%UnlockMyExcel.scp

LabelToVar>Unlock,vScriptData
WriteLn>OpeningScript,wres,vScriptData

ExecuteFile>OpeningScript

XLOpen>ExcelFile,0,xlhandle
DeleteFile>OpeningScript


/*
Unlock:
WaitWindowOpen>Password
Wait>0.3
Crypt>mouse,%Password%,keyboard
Send>keyboard
Wait>0.3
Press Enter
*/

Edit: 08/12/2014 - Fixed the Excel file name in posted script. Had a comma in the name.

[Dominic_Fichera]

Thanks heaps for the reply guys. I do appreciate it.

I've tried your suggestions, but the script gets stuck at the "XLOpen" line and won't go any further and therefore doesn't put in the password. I guess since the document doesn't open completely the command can't finish.

Any suggestions?

Kind regards,

Dominic Fichera

[Djek]

hi Dominic,

be sure ALL instances (processes) of Excel are closed.
Most of the time i got stuck with excel related scripts, was because of there was already a process open.
You must use the taskmanager to check it.
Or kill all processes in you script before actually open one.

kind regards
Djek

[JRL]

Dominic,

I agree with Djck. Something else is wrong if the Excel File isn't opening.

Note that in the line:

Code: Select all

XLOpen>ExcelFile,0,xlhandle

The zero means the file will open but will not be visible. To be able to see the file open, change the zero to a one.

Code: Select all

XLOpen>ExcelFile,1,xlhandle

Also note that if you copied my script above there was a mistake in the excel file name. Instead of ".xlsx" it said ",xlsx". I have replaced the comma with a period so the script should work now.

[Dominic_Fichera]

Hi all,

I have previously tried both of these suggestions. I have worked with some scripts before that use excel and they're not very fun!

I did notice the comma instead of the period and changed this over.

When it isn't visible, the password prompt still appears, but the script is still hanging.

I think it might be an intermittent issue with my computer though because at 1 stage last night I managed to get it working but now it isn't working again. I might try exporting it and try it on my partner's computer to test it out.

Again, thank you so much for all of your help and suggestions! I appreciate it!

Kind regards,

Dominic Fichera

[JRL]

One more thought. I see that Excel gives a password option for opening and a second password option for editing. If you have both of those set, you'll need to deal with two password windows. You might not be hanging up, you might simply be not answering enough questions. Here's a script that will deal with one OR two password prompts.

Code: Select all

Let>Password=pass
Crypt>mouse,Password,Password
Let>ExcelFile=%userdocuments_dir%\MyExcelFile.xlsx
Let>OpeningScript=%temp_dir%UnlockMyExcel.scp
DeleteFile>OpeningScript
LabelToVar>Unlock,vScriptData
WriteLn>OpeningScript,wres,vScriptData
ExecuteFile>OpeningScript
XLOpen>ExcelFile,0,xlhandle
Wait>1
DeleteFile>OpeningScript


*/

/*
Unlock:
WaitWindowOpen>Password
Wait>0.3
Crypt>mouse,%Password%,keyboard
Send>keyboard
Wait>0.3
Press Enter
Let>WW_TIMEOUT=1
WaitWindowOpen>Password
If>WW_Result=TRUE
  Wait>0.3
  Crypt>mouse,%Password%,keyboard
  Send>keyboard
  Wait>0.3
  Press Enter
EndIf
Exit
*/

[Dominic_Fichera]

Hi,

I've double checked that as well. I have manually opened the file and it only needs one password

To be a bit more specific, when I say it hangs, I mean that I am using the debugger and stepping through line by line and when it gets to the XLOpen> part, it doesn't progress to the next line.

Kind regards,

Dominic Fichera

[JRL]

Just stepped through by pressing F8 over and over. All worked properly for me using the last script I posted.

when it gets to the XLOpen> part, it doesn't progress to the next line.

It should not progress until the excel file is open and the password window has been processed. Set the XLOpen>ExcelFile,1,xlhandle line so the excel file will open and be visible and see if the password window opens up. Perhaps the window name is not "Password" for you.

[JRL]

Just occurred to me you may not have the extension .SCP associated with Macro Scheduler. If you run the following, do you get a message?

Code: Select all

Let>OpeningScript=%temp_dir%Test.scp
DeleteFile>OpeningScript
LabelToVar>Test,vScriptData
WriteLn>OpeningScript,wres,vScriptData
ExecuteFile>OpeningScript
Wait>5
DeleteFile>OpeningScript

/*
Test:
message>test
Wait>2
CloseWindow>Macro Scheduler Message
*/

[Dominic_Fichera]

Hi,

I've just tried scrapping all my code and coping yours in and stepping through... But again, it hangs at the line:

Code: Select all

XLOpen>ExcelFile,0,xlhandle

Nothing I try will make it go past here, so nothing after it takes effect... (so, I can't focus on anything or type anything). I've exported it into a .exe as well and it fails on my partner's computer as well. The code isn't progressing past the XLOpen> line... I'm completely out of ideas.

I've also checked, and the window is definitely called "password"... But it's not making it to that command.

Code: Select all

Let>Password=test
Crypt>mouse,Password,Password
Let>ExcelFile=%userdocuments_dir%\MyExcelFile.xlsx
Let>OpeningScript=%temp_dir%UnlockMyExcel.scp
DeleteFile>OpeningScript
LabelToVar>Unlock,vScriptData
WriteLn>OpeningScript,wres,vScriptData
ExecuteFile>OpeningScript
XLOpen>ExcelFile,0,xlhandle
Wait>1
DeleteFile>OpeningScript

*/


Unlock:
WaitWindowOpen>Password
Wait>0.3
Crypt>mouse,%Password%,keyboard
Send>keyboard
Wait>0.3
Press Enter
Let>WW_TIMEOUT=1
WaitWindowOpen>Password
If>WW_Result=TRUE
  Wait>0.3
  Crypt>mouse,%Password%,keyboard
  Send>keyboard
  Wait>0.3
  Press Enter
EndIf
Exit