Questions about _Excel_Open

[gto70070]

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <ScreenCapture.au3>
#include <WindowsConstants.au3>

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
MINE()

Func MINE()

   Local $sFilePath = "C:\Users\USER\OneDrive - edy\library\user.xlsx"
   Local $sPassword = "Xinmenguser"
   Local $vValue = "000"
   Local $oExcel = _Excel_Open(False)
      If @error Then Exit
   $oWorkbook = _Excel_BookOpen ( $oExcel, $sFilePath , False , False , $sPassword )
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $oWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Workbook '" & $sWorkbook & "' has been opened successfully." & @CRLF & @CRLF & "Creation Date: " & $oWorkbook.BuiltinDocumentProperties("Creation Date").Value)
   _Excel_RangeWrite ( $oWorkbook, $oExcel.ActiveSheet, $vValue , "A1" )
   _Excel_BookSave ( $oWorkbook )
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   _Excel_Close ($oExcel)


EndFunc

This is my script

I try to open the existing excel (hide) and write the text

EXCEL has a password

But i got an error
How can i change my script to make it work
Thank you all for your help

Edited March 21, 2020 by gto70070

[gto70070]

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <ScreenCapture.au3>
#include <WindowsConstants.au3>

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
MINE()

Func MINE()
   Local $var = FileSelectFolder("", "")
   DirCreate($var&"\library")
   Sleep(100)
   Local $sFilePath = $var&"\library\client.xlsx"
   Local $sFilePath1 = $var&"\library\object.xlsx"
   Local $sFilePath2 = $var&"\library\user.xlsx"

   Local $sPassword = "client"
   Local $sPassword1 = "Object"
   Local $sPassword2 = "user"
   Local $oExcel = _Excel_Open(False)
      If @error Then Exit
   Local $oBook = _Excel_BookNew($oExcel)
      If @error Then Exit
   _Excel_BookSaveAs($oBook, $sFilePath, Default, True, True, $sPassword)
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   _Excel_Close ($oExcel)

   Sleep(100)
   Local $oExcel = _Excel_Open(False)
      If @error Then Exit
   Local $oBook = _Excel_BookNew($oExcel)
      If @error Then Exit
   _Excel_BookSaveAs($oBook, $sFilePath1, Default, True, True, $sPassword1)
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   _Excel_Close ($oExcel)

   Sleep(100)
   Local $oExcel = _Excel_Open(False)
      If @error Then Exit
   Local $oBook = _Excel_BookNew($oExcel)
      If @error Then Exit
   _Excel_BookSaveAs($oBook, $sFilePath2, Default, True, True, $sPassword2)
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   _Excel_Close ($oExcel)
EndFunc

I found that there may be a wrong setting for my EXCEL password

This is my script. Is it OK for me to set a password for EXCEL?

[water]

Sure, why not?

[gto70070]

@water

I have found the bug and fixed it

Thanks for reply

[water]

Can you please tell us the cause for this bug and how you solved it?
So this thread can be useful for others in the future

[Nine]

4 hours ago, gto70070 said:

Is it OK for me to set a password for EXCEL?

It is pretty much useless.  On Win10 for example, you can use Trio Office, it will open any password protected spreadsheet without having to provide it.

If you want to truly protect a spreadsheet, I would recommend using Crypt UDF...

[gto70070]

@water

very sorry

Here is my solution

Put

_Excel_BookSaveAs($oBook, $sFilePath1, Default, True, True, $sPassword1)

Change to

_Excel_BookSaveAs($oBook, $sFilePath1, Default, True, $sPassword1)

This will make EXCEL add the password normally

@Nine

Thank you very much for telling me
How do I use "Crypt UDF"?
Can I create and open reading or modifying Excel?

[water]

Thanks for describing the problem and your solution

[Nine]

15 minutes ago, gto70070 said:

Can I create and open reading or modifying Excel?

Yes quite easily.  Before opening the excel sheet you will have to decrypt the file, and after saving the sheet you will encrypt it.

Look at the examples of _Crypt_EncryptFile and _Crypt_DecryptFile, it is quite straigthfoward...

[gto70070]

@九

Excuse me

How do I use "Crypto EncryptFile" and "_Crypt DecryptFile" functions for excel encryption
Because I do n’t see a demonstration of file or excel encryption

[Nine]

It is not specific to Excel, you can use it on any type of files.   In the examples, just select test.xls file to encrypt and decrypt.  You will see how it works...

[gto70070]

@Nine

I see it! I'm understanding it.

Can I encrypt the values in excel as well?
Because it contains personal data.

Thank you for always helping me

[Nine]

No need for that, just encrypt the whole file.  It is very robust encryption, nobody will be able to open your file, unless they know your password... 

[gto70070]

@Nine

I understand!
But is there any way to encrypt and decrypt the value
I want to learn more about AUTOIT functions

thank you!

[Nine]

Not sure how Excel will react if you encrypt cell values.  Never tested it.  But yes you encrypt/decrypt pieces of data, using _Crypt_EncryptData and _Crypt_DecryptData.

If you encrypt cell values, you may corrupt the file, I don't know.  Like I said, I never tested it...

[gto70070]

@九
awesome!
thank you!
I hold a learning attitude.

[Nine]

Well, I found a way that seems to work fine (not corrupting the spreadsheet).  You cannot write binary data directly into a cell, but if you convert it to string, Excel accepts it without a problem :

expandcollapse popup

#include <Crypt.au3>
#include <MsgBoxConstants.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open ()
Local $oBook = _Excel_BookOpen ($oExcel, @ScriptDir & "\Test.xls")
Local $sValue = _Excel_RangeRead ($oBook, Default, "A2")
MsgBox ($MB_SYSTEMMODAL,"",$sValue)
Local $sCrypt = StringEncrypt(True, $sValue, "MyPassWord")
MsgBox ($MB_SYSTEMMODAL,"",$sCrypt)
_Excel_RangeWrite ($oBook, Default, String($sCrypt), "A2")
MsgBox ($MB_SYSTEMMODAL,"","Test write encrypt")
_Excel_BookSave ($oBook)
_Excel_BookClose ($oBook)
_Excel_Close ($oExcel)

MsgBox ($MB_SYSTEMMODAL,"","Closed")

Local $oExcel = _Excel_Open ()
Local $oBook = _Excel_BookOpen ($oExcel, @ScriptDir & "\Test.xls")
Local $sValue = _Excel_RangeRead ($oBook, Default, "A2")
MsgBox ($MB_SYSTEMMODAL,"",$sValue)
Local $sCrypt = StringEncrypt(False, $sValue, "MyPassWord")
MsgBox ($MB_SYSTEMMODAL,"",$sCrypt)
_Excel_RangeWrite ($oBook, Default, String($sCrypt), "A2")
MsgBox ($MB_SYSTEMMODAL,"","Test write decrypt")
_Excel_BookSave ($oBook)
_Excel_BookClose ($oBook)
_Excel_Close ($oExcel)

Func StringEncrypt($bEncrypt, $sData, $sPassword)
    _Crypt_Startup() ; Start the Crypt library.
    Local $vReturn = ''
    If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt.
        $vReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4)
    Else
        $vReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4))
    EndIf
    _Crypt_Shutdown() ; Shutdown the Crypt library.
    Return $vReturn
EndFunc   ;==>StringEncrypt

I used help file example as the basis.

Edited March 21, 2020 by Nine

[gto70070]

@ 九

 

Thank you,
I will learn this example

I use the "_Excel_BookOpen" function to get the following error

Edited March 21, 2020 by gto70070
Correct typos and ask new questions

[Nine]

Open help file and read what @error 2 means, it should be obvious

[gto70070]

user.xlsx

$file = FileOpen("C:\Xinmeng\path.txt", 0)
Global $user = FileRead($file)
FileClose($file)
Local $sPassword = "Xinmenguser"
Local $vValue1 = "Nigel"
Local $vValue2 = "gto10010"
Local $vValue3 = "12345678"
Local $Row = 1
Local $oExcel = _Excel_Open()
If @error Then Exit
$oBook = _Excel_BookOpen ( $oExcel, $user , False , True , $sPassword )
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
While 1
   $X = _Excel_RangeRead ( $oBook , $oBook.ActiveSheet, "A"&$Row )
   If $X = "" Then
      $column = $column + 1
   Else
      ExitLoop
   EndIf
WEnd
_Excel_RangeWrite ( $oBook, $oBook.ActiveSheet, $vValue1 , "A"&$Row )
_Excel_RangeWrite ( $oBook, $oBook.ActiveSheet, $vValue2 , "B"&$Row )
_Excel_RangeWrite ( $oBook, $oBook.ActiveSheet, $vValue3 , "C"&$Row )
_Excel_BookSave ( $oBook )
MsgBox(0, "xx", $vValue1)
MsgBox(0, "xx", $vValue2)
MsgBox(0, "xx", $vValue3)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSaveAs Example 1", "Error saving workbook to '" & $oBook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close ($oExcel, True ,True )

@Nine

I have seen example files
And already tried to fix my script.
But it still doesn't work.
How do I modify my script?

path.txtuser.xlsx

Edited March 21, 2020 by gto70070
Upload new file