#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=\\tbm-jobserver01\c$\install\ScheduledTasks\Export-Excel-to-PDF-Projektstatus.exe
#AutoIt3Wrapper_Res_Fileversion=1.0.0.21
#AutoIt3Wrapper_Res_Fileversion_AutoIncrement=p
#AutoIt3Wrapper_Res_SaveSource=y
#AutoIt3Wrapper_Res_Language=1031
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; *** Start added by AutoIt3Wrapper ***
; *** End added by AutoIt3Wrapper ***
; Autoit Version v3.3.14.5

#include <ExcelConstants.au3>
#include <MsgBoxConstants.au3>
#include <Timers.au3>
#include <File.au3>
#include <Excel.au3>
#include <Constants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Date.au3>

Global Const $inputDir = "H:\daten\Projekte (Betriebsauftrag)\Projektstatus\TBx Status"
Global Const $outputDir = $inputDir & "\Archiv"
Global Const $xls = ".xls"
Global Const $xlsx = ".xlsx"
Global Const $XLMatch = "*.XLS?"
Global Const $pdf = ".pdf"
Global Const $xlPaperA4 = 9, $xlPortrait = 1

$LogFile = $outputDir & "\LOG\" & @YEAR & "-" & @MON & "-" & @MDAY & "@" & @HOUR & "-" & @MIN & "-" & @SEC & "__@XL-2-PDF-logfile.TXT"
Global $hLOG = FileOpen($LogFile, 1 + 8)
If $hLOG = -1 Then
	MsgBox($MB_SYSTEMMODAL, "", "An error occurred when reading the log file, exiting")
	Exit
EndIf

$TailPID=ShellExecute("Z:\Tail.exe",' -f "' & $LogFile & '"')

$Start = TimerInit()
FileWriteLine($hLOG, " ")
_FileWriteLog($hLOG, "°°°°°°°°°°°° Start: " & _NowCalc() & " °°°°°°°°°°°°°°°")
_FileWriteLog($hLOG, "Script: " & @ScriptFullPath & @CRLF)
_FileWriteLog($hLOG, "Script Version: " & FileGetVersion(@ScriptFullPath) & ", Script zuletzt geändert: " & FileGetTime(@ScriptFullPath,0,1))
_FileWriteLog($hLOG, "User: " & @LogonDomain & "\" & @UserName & @CRLF)
_FileWriteLog($hLOG, "Host: " & @ComputerName & @CRLF)
_FileWriteLog($hLOG, "Input Dir: " & $inputDir)
_FileWriteLog($hLOG, "Archive Dir: " & $outputDir)
_FileWriteLog($hLOG, "Excel File Search Pattern: " & $XLMatch)
_FileWriteLog($hLOG, " - - - - - - - - - - - - - - - - - - - - - - - -" & @CRLF)
If ProcessExists("excel.exe") Then
	_FileWriteLog($hLOG, "EXCEL.EXE is running, trying to terminate all EXCEL processes...")

	$notausgang = 0
	While ProcessExists
		ProcessClose("Excel.exe")
		Sleep(1000)
		$notausgang += 1
		If $notausgang > 10 Then
			_FileWriteLog($hLOG, "FATAL ERROR: Excel.exe process could *NOT* be terminated! --> exiting script at " & _NowCalc())
			FileClose($hLOG)
			Exit
		EndIf
	WEnd
Else
 ; keine Excel Instanz offen --> passt!
EndIf




Global $i = 0
Global $NextXLS = ""
Global $sPrintArea

SearchxlsFiles()
_FileWriteLog($hLOG, "############### Done, Processing Time = " & StringFormat("%.2f", Round(_Timer_Diff($Start) / 1000, 2)) & " Sec. ################" & @CRLF)
FileClose($hLOG)

ProcessClose($TailPID)
$pidLOG=ShellExecute($LogFile)
ProcessClose("Excel.exe")

Sleep(60*10*1000)
ProcessClose($pidLOG)



Func SearchxlsFiles()
	$aXLS = _FileListToArray($inputDir, $XLMatch, 1, 0) ; files only, no FPFN
	; _ArrayDisplay($aXLS)
	If @error Or $aXLS[0] = 0 Then
		_FileWriteLog($hLOG, "Nothing to do: No XLS* files found in " & $inputDir)
		Return False
	Else
		Local $L
		For $L = 1 To $aXLS[0]

			$NextXLS = $aXLS[$L]

			_FileWriteLog($hLOG, "File " & $L & " of " & $aXLS[0] & ": " & $NextXLS & @CRLF)
			$RegEx2PDF = "(?i)^(.*)(.xls[a-z]?)$"
			$RegEx2PDFrepl = "$1_" & @YEAR & "-" & @MON & "-" & @MDAY & "\.PDF"
			If StringRegExp($NextXLS, $RegEx2PDF) Then
				$PDFname = StringRegExpReplace($NextXLS, $RegEx2PDF, $RegEx2PDFrepl)
				If FileExists($outputDir & "\" & $PDFname) Then
					_FileWriteLog($hLOG, @TAB & "Done already: " & $outputDir & "\" & $PDFname & @CRLF)
				Else
					_FileWriteLog($hLOG, @TAB & @TAB & "Start XL to PDF print processing" & @CRLF)
					ExcelPrint($inputDir & "\" & $NextXLS, $outputDir & "\" & $PDFname)
				EndIf
			Else
				_FileWriteLog($hLOG, @TAB & "No Excel File, skipping: " & $inputDir & "\" & $NextXLS & @CRLF)
			EndIf
			; _FileWriteLog($hLOG,"Closing EXCEL.exe" &@CRLF)
			; ProcessClose("EXCEL.exe")
			; ProcessWaitClose("EXCEL.exe")
		Next
	EndIf
	_FileWriteLog($hLOG, "END XLS Search -> " & @CRLF)
EndFunc   ;==>SearchxlsFiles


Func ExcelPrint($_XL_FPFN, $_PDF_FPFN)

	; MsgBox(0,@ScriptLineNumber,"in ExcelPrint")
	_FileWriteLog($hLOG, @TAB & @TAB & @TAB & "in FUNC ExcelPrint" & @CRLF)
	Local $oAppl = _Excel_Open()
	If @error Then _FileWriteLog($hLOG, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
	; MsgBox(0,@ScriptLineNumber,"Nach Excel Open")
	Global $oWorkbook = _Excel_BookOpen($oAppl, $_XL_FPFN)
	If @error Then
		_FileWriteLog($hLOG, "Error opening workbook '" & $_XL_FPFN & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
		_Excel_Close($oAppl)
		Return False
	EndIf
	; MsgBox(0,@ScriptLineNumber,"Nach Excel Book Open")
	Global $aSheets = _Excel_SheetList($oWorkbook)
	If @error Then
		_FileWriteLog($hLOG, "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
		Return False
	EndIf
	; MsgBox(0,@ScriptLineNumber,"Nach Excel SheetList")
	For $i = 0 To UBound($aSheets) - 1
	    _FileWriteLog($hLOG," - - -  $i = " & $i & "  - - -")
		If $aSheets[$i][1].Visible <> $xlSheetVisible Then
			_FileWriteLog($hLOG,@TAB & "Sheet #" & $i & " is *NOT* visible --> skipping this sheet!")
			ContinueLoop
		EndIf
		$oAppl.PrintCommunication = False
		$aSheets[$i][1].PageSetup.PaperSize = $xlPaperA4
		$aSheets[$i][1].PageSetup.Orientation = $xlPortrait
		$aSheets[$i][1].PageSetup.FitToPagesWide = 1
		$aSheets[$i][1].PageSetup.FitToPagesTall = 10000
		$aSheets[$i][1].PageSetup.PrintArea = $sPrintArea
		If @error Then _FileWriteLog($hLOG, "Error setting page layout." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
		$oAppl.PrintCommunication = True

		_FileWriteLog($hLOG, "Trying to calculate pages" & @CRLF)
		Global $iNumberOfPages = ($aSheets[$i][1].HPageBreaks.Count + 1)
		_FileWriteLog($hLOG, "Number of pages in '" & $aSheets[$i][0] & "' : " & $iNumberOfPages & @CRLF)
		If @error Then _FileWriteLog($hLOG, "Error setting page layout." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
		$oAppl.PrintCommunication = True
	Next

	;-------------------------------------------------------------------------------------------------------------------------------------------------------
	;FileClose($hLOG)
	;FileClose($procfiles)
	;Exit ; HUE
	_Excel_Export($oAppl, $oWorkbook, $_PDF_FPFN, Default, Default, Default, Default, Default,False)
	;-------------------------------------------------------------------------------------------------------------------------------------------------------
	If @error Then _FileWriteLog($hLOG, "Error saving the workbook to '" & $_PDF_FPFN & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
	_FileWriteLog($hLOG, "The whole workbook was successfully exported as '" & $_PDF_FPFN & "'.")
	_Excel_Close($oAppl)
EndFunc   ;==>ExcelPrint