Jump to content
Sign in to follow this  
nasar

Excel - opening with a password

Recommended Posts

nasar

Hi,

I have a small application that uses an excel file to store data. At this point my excel file does not have a password but I would like to set one and I am bit lost - so that users do not have access to open the file other than through my application (I am okay to hardcode the password). Since im using the following code to open and write to the file - appreciate your help with this - excuse the terrible programming skills and borrowed code... :)

Also at no point do i want the users to see the excel file opening and all that - ideally if all of this can be done without the users seeing the fiel opening and all that it would be great - I was trying to avoid using the SEND fucntion.

GUISetState(@SW_SHOW)
While 1
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
Exit
Case $SaveButton
GUISetState(@SW_DISABLE, $WinMain)
; Stops you from changing anything

$FileName=@ScriptDir & "\Worksheet1.xls"
if not FileExists($FileName) then ; Just a check to be sure..
Msgbox (0,"Excel Data Test","Error: Can't find file " & $FileName)
Exit
endif
$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename


If (not @error) and IsObj($oExcelDoc) then ; Check again if everything went well
$iRowCount = $oExcelDoc.ActiveSheet.UsedRange.Rows.Count
$iRowCount = $iRowCount + 1

$oExcelDoc.saved=1 ; Prevent questions from excel to save the file
$oExcelDoc.close

EndIf
WEnd
Edited by nasar

Share this post


Link to post
Share on other sites
JLogan3o13

Hi, nasar, welcome to the forum. I would take a look at the _Excel functions in the help file. If you use _ExcelBookOpen to open your file, you can set both the visibility of the file and a password on it.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
nasar

Hi, nasar, welcome to the forum. I would take a look at the _Excel functions in the help file. If you use _ExcelBookOpen to open your file, you can set both the visibility of the file and a password on it.

Thank you for your prompt reply - I did explore that a while ago while waiting. But it looks like I may have to move away from creating an object and assign the excel file to it and in which case I then get lost as to the erro handling that is part of teh 'borrowed' code i have used ie. If (not @error) and IsObj($oExcelDoc) then ;

Share this post


Link to post
Share on other sites
DW1

This should get you on the right path:

Global Const $xlNormal = -4143

Save()

Func Save()
    Local $FileName = @ScriptDir & "\Worksheet1.xls"
    Local $sPassword = 'MyPass'
    If Not FileExists($FileName) Then ; Just a check to be sure..
        MsgBox(0, "Excel Data Test", "Error: Can't find file " & $FileName)
        Exit
    EndIf

    Local $oExcel = ObjCreate("Excel.Application")

    If @error Or (Not IsObj($oExcel)) Then ; Check again if everything went well
        Return
    EndIf

    ;Set to not show excel
    $oExcel.Visible = 0

    ;Save current alert settings
    Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    Local $fScreenUpdating = $oExcel.Application.ScreenUpdating

    ;Set alert settings to not show
    $oExcel.Application.DisplayAlerts = 0
    $oExcel.Application.ScreenUpdating = 0

    ;Open the file using a password
    $oExcel.WorkBooks.Open($FileName, Default, 0, Default, $sPassword, Default)

    ;Select the first sheet in the current workbook
    $oExcel.ActiveWorkbook.Sheets(1).Select()

    ;Perform your actions
    $iRowCount = $oExcel.ActiveSheet.UsedRange.Rows.Count
    $iRowCount = $iRowCount + 1

    ;Save the file with a password
    $oExcel.ActiveWorkBook.SaveAs($FileName, $xlNormal, $sPassword, Default, Default, Default, 1, 2)

    ;Close the workbook
    $oExcel.ActiveWorkbook.Close()

    ;Return original alert settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating

    ;Quit the application and release the resources
    $oExcel.Application.Quit()
    $oExcel = ''
EndFunc   ;==>Save

Share this post


Link to post
Share on other sites
water

With _ExcelBookOpen you can pass a password. After calling the function check for @error <> 0.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nasar

Thanks danwilli!!!!!. This is what i have done with the code now... :)....it seems to be saving fine- just that it takes longer than earlier...not sure if it is because of the way the excel object is being created and all that....again excuse the terrible lengthy lines of simplified coding...I also commented out the global constant cos it kept giving error cannot redefine constant/variable...still seems to be working though.

#include <GuiConstantsEx.au3>
#include <AVIConstants.au3>
#include <TreeViewConstants.au3>
#include <misc.au3>
#include <String.au3>
#include <ExcelCOM_UDF.au3>
#include <date.au3>
#include <DateTimeConstants.au3>
#include <WindowsConstants.au3>

;~ Global Const $xlNormal = -4143
Global $WinMain, $ii, $SaveButton, $MyExcel, $CellRange, $aArray, $oExcelDoc, $oDocument, $iRowCount, $varDate, $handle, $user, $varDateData, $varTime, $varTimeData, $radio, $radioData, $radioDataText, $radio1, $radio2, $radio3, $radio4, $radio5, $radio6, $radioGroup, $Clients, $GetOut, $Dropdown, $radioclear, $Remarks, $RemarksText

; GUI
$WinMain = GUICreate("Service Request Logger", 400, 400)
$handle = WinGetHandle ( "Service Request Logger")
$user = @UserName

; BUTTON
$SaveButton = GUICtrlCreateButton("Save", 10, 330, 100, 30)

; PIC
;GUICtrlCreateLabel("Service Request Logger", 75, 1, 153, 15)
GUICtrlSetBkColor(-1, 0xFFFFFF)
GUICtrlSetColor(-1, 0x000000)

; LIST
GUICtrlCreateLabel("Clients:",5,10,60,30)
$Clients = GUICtrlCreateList("", 5, 30, 155, 250)
GUICtrlSetData(-1, "Client1|Client2|Client3", "")

; GROUP WITH RADIO BUTTONS
$radioGroup = GUICtrlCreateGroup("Service Type", 170, 7,225,268)
$radio1 = GUICtrlCreateRadio("Test1", 175, 25, 95) ;GUICtrlSetState(-1, $GUI_CHECKED)
$radio2 = GUICtrlCreateRadio("Test2", 175, 45, 95)
$radio3 = GUICtrlCreateRadio("Test3", 175, 65, 95)
$radio4 = GUICtrlCreateRadio("Test4", 175, 85, 95)
$radio5 = GUICtrlCreateRadio("Test5", 175, 105, 95)
$radio6 = GUICtrlCreateRadio("Test6", 175, 125, 95)

$radio7 = GUICtrlCreateRadio("Test7", 175, 145, 95)
$radio8 = GUICtrlCreateRadio("Test8", 175, 165, 95)
$radio9 = GUICtrlCreateRadio("Test9", 175, 185, 95)

$radio10 = GUICtrlCreateRadio("Test10", 175, 205, 95)
$radio11 = GUICtrlCreateRadio("Test11", 175, 225, 95)
$radio12 = GUICtrlCreateRadio("Test12", 175, 245, 95)

$radio13 = GUICtrlCreateRadio("Test13", 290, 25, 95)
$radio14= GUICtrlCreateRadio("Test14", 290, 45, 95)
$radio15 = GUICtrlCreateRadio("Test15", 290, 65, 95)
$radio16 = GUICtrlCreateRadio("Test16", 290, 85, 95)
$radio17 = GUICtrlCreateRadio("Test17", 290, 105, 95)

$radio18 = GUICtrlCreateRadio("Test18", 290,125,95)
$radio19 = GUICtrlCreateRadio("Test19", 290,145,99)
$radio20 = GUICtrlCreateRadio("Test20", 290,165,95)
$radio21 = GUICtrlCreateRadio("Test21", 290,185,95)
$radio22 = GUICtrlCreateRadio("Test22", 290,205,95)
$radio23 = GUICtrlCreateRadio("Test23", 290, 225, 95)
GUICtrlCreateGroup("", -99, -99, 1, 1) ;close group

; COMBO
$Dropdown = GUICtrlCreateCombo("", 170, 280, 140, 100)
GUICtrlSetData(-1,"New Case|Follow-up","New Case")

; INPUT
GUICtrlCreateLabel("Remarks:", 170,305)
$Remarks = GUICtrlCreateInput("", 168, 320, 225, 70)

; DATE
$varDate = GUICtrlCreateDate("", 5, 280, 130, 20)
;$varTime = GUICtrlCreateDate("", 140, 280, 70, 20, $DTS_TIMEFORMAT)

GUISetState(@SW_SHOW)
While 1
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
Exit
Case $SaveButton
GUISetState(@SW_DISABLE, $WinMain)
Local $FileName = @ScriptDir & "\Worksheet1.xls"
Local $sPassword = 'test123'
If Not FileExists($FileName) Then ; Just a check to be sure..
MsgBox(0, "Excel Data Test", "Error: Can't find file " & $FileName)
Exit
EndIf

Local $oExcel = ObjCreate("Excel.Application")

If @error Or (Not IsObj($oExcel)) Then ; Check again if everything went well
Return
EndIf

;Set to not show excel
$oExcel.Visible = 0

;Save current alert settings
Local $fDisplayAlerts = $oExcel.Application.DisplayAlerts
Local $fScreenUpdating = $oExcel.Application.ScreenUpdating

;Set alert settings to not show
$oExcel.Application.DisplayAlerts = 0
$oExcel.Application.ScreenUpdating = 0

;Open the file using a password
$oExcel.WorkBooks.Open($FileName, Default, 0, Default, $sPassword, Default)

;Select the first sheet in the current workbook
$oExcel.ActiveWorkbook.Sheets(1).Select()

;Perform your actions
$GetOut = "N"
$iRowCount = $oExcel.ActiveSheet.UsedRange.Rows.Count
$iRowCount = $iRowCount + 1

$varDateData = GUICtrlRead ($varDate)
$varTimeData = GUICtrlRead ($varTime)

If GUICtrlRead($radio1)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio1,1)
ElseIf GUICtrlRead($radio2)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio2,1)
ElseIf GUICtrlRead($radio3)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio3,1)
ElseIf GUICtrlRead($radio4)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio4,1)
ElseIf GUICtrlRead($radio5)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio5,1)
ElseIf GUICtrlRead($radio6)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio6,1)
ElseIf GUICtrlRead($radio7)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio7,1)
ElseIf GUICtrlRead($radio8)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio8,1)
ElseIf GUICtrlRead($radio9)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio9,1)
ElseIf GUICtrlRead($radio10)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio10,1)
ElseIf GUICtrlRead($radio11)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio11,1)
ElseIf GUICtrlRead($radio12)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio12,1)
ElseIf GUICtrlRead($radio13)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio13,1)
ElseIf GUICtrlRead($radio14)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio14,1)
ElseIf GUICtrlRead($radio15)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio15,1)
ElseIf GUICtrlRead($radio16)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio16,1)
ElseIf GUICtrlRead($radio17)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio17,1)
ElseIf GUICtrlRead($radio18)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio18,1)
ElseIf GUICtrlRead($radio19)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio19,1)
ElseIf GUICtrlRead($radio20)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio20,1)
ElseIf GUICtrlRead($radio21)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio21,1)
ElseIf GUICtrlRead($radio22)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio22,1)
ElseIf GUICtrlRead($radio23)=$GUI_CHECKED Then
$radioData=GUICtrlRead ($radio23,1)
Else
Msgbox (0,"Not Saving","Please select service type")
$GetOut = "Y"
EndIf

If GUICtrlRead ($Clients) ="" Then
Msgbox (0,"Not Saving","Please select Customer")
$GetOut = "Y"
EndIf

$RemarksText = GUICtrlRead($Remarks)

If $GetOut = "N" Then
WITH $oExcel.ActiveSheet
.range("A"& $iRowCount & ":A" & $iRowCount).value = $user
.range("B"& $iRowCount & ":B" & $iRowCount).value = $varDateData
;~ .range("C"& $iRowCount & ":C" & $iRowCount).value = $varTimeData
.range("D"& $iRowCount & ":D" & $iRowCount).value = $radioData
.range("E"& $iRowCount & ":E" & $iRowCount).value = GUICtrlRead ($Clients,1)
.range("F"& $iRowCount & ":F" & $iRowCount).value = GUICtrlRead ($Dropdown)
.range("G"& $iRowCount & ":G" & $iRowCount).value = _NowDate()
.range("H"& $iRowCount & ":H" & $iRowCount).value = _NowTime(5)
.range("I"& $iRowCount & ":I" & $iRowCount).value = $RemarksText
$oExcel.Windows(1).Visible = True ; Otherwise the worksheet window will be saved 'hidden'




;Save the file with a password
;~ $oExcel.ActiveWorkBook.SaveAs($FileName, $xlNormal, $sPassword, Default, Default, Default, 1, 2)
$oExcel.ActiveWorkBook.Save

;Close the workbook
$oExcel.ActiveWorkbook.Close()
Msgbox (0,"Saved","Data saved")


;Return original alert settings
$oExcel.Application.DisplayAlerts = $fDisplayAlerts
$oExcel.Application.ScreenUpdating = $fScreenUpdating

;Quit the application and release the resources
$oExcel.Application.Quit()
$oExcel = ''
ENDWITH
GUICtrlSetState($radio1, $GUI_UNCHECKED)
GUICtrlSetState($radio2, $GUI_UNCHECKED)
GUICtrlSetState($radio3, $GUI_UNCHECKED)
GUICtrlSetState($radio4, $GUI_UNCHECKED)
GUICtrlSetState($radio5, $GUI_UNCHECKED)
GUICtrlSetState($radio6, $GUI_UNCHECKED)
GUICtrlSetState($radio7, $GUI_UNCHECKED)
GUICtrlSetState($radio8, $GUI_UNCHECKED)
GUICtrlSetState($radio9, $GUI_UNCHECKED)
GUICtrlSetState($radio10, $GUI_UNCHECKED)
GUICtrlSetState($radio11, $GUI_UNCHECKED)
GUICtrlSetState($radio12, $GUI_UNCHECKED)
GUICtrlSetState($radio13, $GUI_UNCHECKED)
GUICtrlSetState($radio14, $GUI_UNCHECKED)
GUICtrlSetState($radio15, $GUI_UNCHECKED)
GUICtrlSetState($radio16, $GUI_UNCHECKED)
GUICtrlSetState($radio17, $GUI_UNCHECKED)
GUICtrlSetState($radio18, $GUI_UNCHECKED)
GUICtrlSetState($radio19, $GUI_UNCHECKED)
GUICtrlSetState($radio20, $GUI_UNCHECKED)
GUICtrlSetState($radio21, $GUI_UNCHECKED)
GUICtrlSetState($radio22, $GUI_UNCHECKED)
GUICtrlSetState($radio23, $GUI_UNCHECKED)
GUICtrlSetState($Clients, $GUI_UNCHECKED)
GUICtrlSetState ($Dropdown,$GUI_UNCHECKED)
GUICtrlSetData ( $Remarks, "")
GUISetState(@SW_ENABLE, $WinMain)
WinActivate($handle)
EndIf
EndSwitch
WEnd
Edited by nasar

Share this post


Link to post
Share on other sites
water

Why don't you use the Excel UDF that comes with AutoIt to handle your Excel files? You get much more support than for the ExcelCOM UDF.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×