eingebettetes Excel aktivieren

#include <GUIConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <File.au3>
#include <Crypt.au3>

Opt("GUIOnEventMode", 1)

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ;Initialize my error handler

Func CloseClicked() ;Function to close the Programm
$oExcelDoc.Close (0)
Local $iDelete = FileDelete(@TEMPdir & "\Test.csv")
Exit
EndFunc

$FileName = FileOpenDialog("Pick a file", @ScriptDir, "Excel Files (*.xls;*.xlsx;*.xlsm)") ;Excel file to be worked on is chosen

if not FileExists($FileName) then ;test if chosen Excel file exists
Msgbox (0,"Excel File Test","Can't run this test, because it requires an Excel file in "& $FileName)
Exit
endif
;define variables
;---------------------------------
Local $oExcelDoc ;Variable for Created Excel Object
Local $arr[16] ;Array to define Hex vlaues
$arr[0] = 0
$arr[1] = 1
$arr[2] = 2
$arr[3] = 3
$arr[4] = 4
$arr[5] = 5
$arr[6] = 6
$arr[7] = 7
$arr[8] = 8
$arr[9] = 9
$arr[10] = "A"
$arr[11] = "B"
$arr[12] = "C"
$arr[13] = "D"
$arr[14] = "E"
$arr[15] = "F"
Local $array ;variable to save .csv file as an array

;---------------------------------
$oExcelDoc = ObjGet($FileName) ;Get an Excel Object from the chosen file
if IsObj($oExcelDoc) then ;check if creation of object worked out
GUICreate ( "Embedded ActiveX Test", 1640, 950) ;create the GUI
GUISetOnEvent ($GUI_EVENT_CLOSE, "CloseClicked") ;define event to close the object

$GUI_ActiveX = GUICtrlCreateObj ( $oExcelDoc, 0, 200 , 1640 , 800) ;embed the chosen Excel file into GUI

For $Bar In $oExcelDoc.CommandBars ;set commandbars invisible
If $Bar.Enabled = True Then $Bar.Enabled = False
If $Bar.Visible = True Then $Bar.Visible = False
Next ;set different commandbars and menu bars invisible
$oExcelDoc.Application.DisplayFormulaBar = False
$oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
$oExcelDoc.Application.CommandBars("Control Toolbox").Visible = False
$oExcelDoc.Application.CommandBars("Worksheet Menu Bar").Enabled = False
$oExcelDoc.Application.DisplayScrollBars = True
$oExcelDoc.Application.DisplayStatusBar = False
$oExcelDoc.Application.DisplayFormulaBar = False
;---------------------------------------------------------------------------------
;create buttons, labels and input fields and put them into GUI
$SoftwareButton = GUICtrlCreateButton("show all info", 20, 150)
GUICtrlSetOnEvent($SoftwareButton, "SoftwareButtonClicked")

$CoolingButton = GUICtrlCreateButton("show cooling parameter", 90, 150)
GUICtrlSetOnEvent($CoolingButton, "CoolingButtonClicked")

$EEPCreateButton = GUICtrlCreateButton("Create EEP File", 720, 150)
GUICtrlSetOnEvent($EEPCreateButton, "CreateButtonClicked")

$EEPNameLabel = GUICtrlCreateLabel("name of .eep file:",220, 157)

$EEPNameInput = GUICtrlCreateInput("", 320, 155, 70, 20)

$EEPEndingLabel = GUICtrlCreateLabel(".eep", 400, 157)

$UserNameLabel = GUICtrlCreateLabel("User: ", 440, 157)

$UserNameInput = GUICtrlCreateInput("", 480, 155, 70, 20)

$CommentLabel = GUICtrlCreateLabel("Remark: ", 560, 157)

$CommentInput = GUICtrlCreateInput("", 600, 155, 100, 20)
;-------------------------------------------------------------------------------
CoolingButtonClicked() ;show only cooling technology relevant parameters as default status
GUISetState (@SW_SHOW) ;Show GUI

; GUI Message loop
While 1
$msg = GUIGetMsg()
Select
Case $msg = $GUI_EVENT_CLOSE ;or $msg = $GUI_FileExit
ExitLoop
EndSelect
Wend

GUIDelete ()

;close workbook, otherwise Excel will stay in memory after the script exits
$oExcelDoc.Close (0) ; Close the Excel workbook - Save prompt will not open

EndIf

Func SoftwareButtonClicked() ;SoftwareButton is clicked = all parameters are shown in GUI

Local $sPasswd = InputBox("Security Check", "Enter your password.", "", "*") ; Asks the user to enter a password. Don't forget to validate it!
$hash = _Crypt_HashData($sPasswd, $CALG_MD5)
if $hash == "0x91273E8CFB21C51C025CE8DE2A9231EE" then
$oExcelDoc.Application.Run("Software") ;all the regulations of the "cooling technology" setting are canceled and every single cell can be seen and changed
else
Msgbox(0, "Warning", "The password you entered is not right!")
endif

EndFunc

Func CoolingButtonClicked() ;CoolingButton is clicked = only cooling technology parameters are shown in GUI
$oExcelDoc.Application.Run("BlattschutzAufheben") ;sheet protection is canceled, so that the "cooling technology" regulations can be applied
$oExcelDoc.Application.Run("SelectAll") ;every single classification is activated so that the "cooling technology" regulations can "see" all points from which the relevant ones are chosen
$oExcelDoc.Application.Run("CoolingTechnology") ;relevant cooling parameters are chosen and a sheet protection is set so that only the relevant cells can be changed
EndFunc

Func CreateButtonClicked() ;Function which defines what happens when the "Create EEP file" button is clicked
; GUICtrlSetState($oExcelDoc.Application, 0)
;ControlFocus($oExcelDoc, "", "")
;WinActivate($oExcelDoc)
#cs
$Control = ControlGetFocus("$oExcelDoc")

If Not $Control Then
MsgBox(16, "Error", "Control not focused")
Else
MsgBox(0, "", $Control)
EndIf
#ce
$UserInput = GUICtrlRead($UserNameInput) ;input field for user name is read
$EEPInput = GUICtrlRead($EEPNameInput) ;input field for EEP-file name is read
$Comment = GUICtrlRead($CommentInput) ;input field for comment is read

If $EEPInput == "" Then ;routine that ensures that the EEP file gets a name
MsgBox(0,"","you have to give your EEP file a name!")
Else
$oExcelDoc.Application.Run("SaveAsCsv") ;if the EEP file got a name, a csv file is created

Local $file = FileOpen($EEPInput & ".eep", 1) ;a textfile with the chosen EEP-file name is created with the ending .eep
_FileReadToArray(@TEMPdir & "\Test.csv", $array) ;created csv file is saved in an array

;insert header in .eep file
FileWrite($file, "################################################################################" & @CRLF)
FileWrite($file, "#Created with Excel to EEP programme" & @CRLF)
FileWrite($file, "#User: " & $UserInput & " Date: " & @MDAY & "." & @MON & "." & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & @CRLF)
FileWrite($file, "#Project File: " & $EEPInput & ".eep" & @CRLF)
FileWrite($file, "#Remarks: " & $Comment & @CRLF)
FileWrite($file, "################################################################################" & @CRLF)
FileWrite($file, @CRLF)

For $zeile = 1 To $array[0] ;array is being worked with

$wert = StringSplit($array[$zeile], ";") ;values in between semicolons are being separated and written into $wert

$number = number($wert[1]) ;values of first collumn of array are converted into a number

Local $Zahl1 = 4096 ;this number is being calculated,
Local $Zahl2 = 256 ;so that a hex number with 4 characters develops
Local $Zahl3 = 16 ;this is done with the array which was defined at the top of the programm
$Ziffer1 = Int($number/$Zahl1) ;(from 0 to F)
$Rest1 = Mod($number,$Zahl1)
$Ziffer2 = Int($Rest1/$Zahl2)
$Rest2 = Mod($Rest1,$Zahl2)
$Ziffer3 = Int($Rest2/$Zahl3)
$Ziffer4 = Mod($Rest2,$Zahl3)

Local $ArrString = $arr[$Ziffer1] & $arr[$Ziffer2] & $arr[$Ziffer3] & $arr[$Ziffer4] ;4 digit hex number (calculated above) is put together

if $wert[2] == "CalculateCRC" then ;first line has to be read separately,
FileWrite($file, hex($wert[1],4)) ;because its adress is "0"
FileWrite($file, " " & hex($wert[6],2) & ";") ;and many other non relevant parameters have this adress too
FileWrite($file, " " & $wert[5] & ";") ;...so the line for the first parameter is done seperately
FileWrite($file, " " & $wert[4] & @CRLF) ;in this if-statement

elseif $ArrString <> "0000" then ;for all the other parameters
FileWrite($file, $arr[$Ziffer1] & $arr[$Ziffer2] & $arr[$Ziffer3] & $arr[$Ziffer4]) ;this if statement creates the lines
FileWrite($file, " " & hex($wert[6],2) & ";") ;in the .eep file
FileWrite($file, " " & $wert[5] & ";")
FileWrite($file, " " & $wert[4] & @CRLF)
EndIf
Next
MsgBox(0,"","EEP file is saved at the location your xlsm file is at!") ;Messagebox to show the routine is done and to show where the .eep file is saved
EndIf
EndFunc

Func MyErrFunc() ;This is the custom error handler
$HexNumber=hex($oMyError.number,8)
Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _
"err.description is: " & @TAB & $oMyError.description & @CRLF & _
"err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
"err.number is: " & @TAB & $HexNumber & @CRLF & _
"err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
"err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
"err.source is: " & @TAB & $oMyError.source & @CRLF & _
"err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
"err.helpcontext is: " & @TAB & $oMyError.helpcontext _
,1)
SetError(1) ;to check for after this function returns
Endfunc

;DirRemove(@TempDir & '\Test.csv')
;FileDelete(@TEMPdir & "\Test.csv")
Exit ;Ende des Scripts