Excel - opening with a password

[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 March 13, 2013 by nasar

[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.

[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 ;

[DW1]

This should get you on the right path:

expandcollapse popup

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

[water]

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

[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.

expandcollapse popup

#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 March 14, 2013 by nasar

[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.