Convert word&excel files to PDF

[JustSomeone]

Posting this, as i promised.

Special thanks to water for his efforts and wasted time

What's the idea : 

Monitor a folder x, if there is an word or excel file inside it - fit it to 1 page tall,  convert it to pdf in another folder , record the name of the file to a log but keep the source. So far this is done, and it seems like its working. The problem is :

How in the name of $%^&*() to make it read the amount of pages. If i manage to get this value correctly, i will make it split the PDFs to some reasonable amount of pages, because i use extremly large documents and if the pdf becomes 50ish+ pages i get another problems.

Note: it seems it is showing the correct amount of pages on water's pc, but i tried 4 different setups and i did not managed to get the correct amount of pages.

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Word.au3>
#include <Constants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Date.au3>

Global Const $inputDir = "c:\DocInput\"
Global Const $outputDir = "c:\Output\"
Global Const $xls = ".xls"
Global Const $xlsx = ".xlsx"
Global Const $doc = ".doc"
Global Const $docx = ".docx"
Global Const $pdf = ".pdf"
Global Const $xlPaperA4 = 9, $xlPortrait = 1
Global $logfile = FileOpen(@ScriptDir & "\logfile.log", $FO_READ + $FO_APPEND)
If $logfile = -1 Then
    MsgBox($MB_SYSTEMMODAL, "", "An error occurred when reading the log file, exiting")
    Exit
EndIf
Global $procfiles = FileOpen(@ScriptDir & "\processed.log", $FO_READ + $FO_APPEND)
If $procfiles = -1 Then
    MsgBox($MB_SYSTEMMODAL, "", "An error occurred when reading the processed file, exiting")
    Exit
EndIf
Global $procarray = FileReadToArray(@ScriptDir & "\processed.log")
If @error Then MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the processed files log. @error: " & @error)
Global $i = 0
Global $sFileName = ""
Global $sPrintArea

Func SearchdocFiles()
    _FileWriteLog($logfile, "Begin DOC search ->" & @CRLF)

    Local $dSearch = FileFindFirstFile($inputDir & "*.doc*")
    If $dSearch = -1 Then
        Sleep(10)
        _FileWriteLog($logfile, "No Word files found." & @CRLF)
    EndIf

    While 1
        $sFileName = FileFindNextFile($dSearch)
        If @error Then
            ExitLoop
        Else
            _FileWriteLog($logfile, "Found file : " & $sFileName & @CRLF)
            Local $iIndex = _ArraySearch($procarray, $sFileName, 0, 0, 1)
            If @error Then
                _FileWriteLog($logfile, '"' & $sFileName & '" was not processed. Converting ...' & @CRLF)
                Sleep(10)
                WordPrint()
            Else
                _FileWriteLog($logfile, '"' & $sFileName & '" was been processed, Skipping ...' & @CRLF)
            EndIf
        EndIf
        _FileWriteLog($logfile, "Closing winword.exe" & @CRLF)
        ProcessClose("WINWORD.exe")
        ProcessWaitClose("WINWORD.exe")
    WEnd

    FileClose($dSearch)
    _FileWriteLog($logfile, "END DOC Search -> " & @CRLF)
EndFunc   ;==>SearchdocFiles

Func WordPrint()
    Global $oWord = _Word_Create()
    If @error <> 0 Then _FileWriteLog($logfile, @CRLF & _
            "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    _FileWriteLog($logfile, "Preparing to open file : " & $inputDir & $sFileName & @CRLF)
    Global $oDoc = _Word_DocOpen($oWord, $inputDir & $sFileName, Default, Default, True)
    If @error <> 0 Then _FileWriteLog($logfile, @CRLF & _
            "Error opening " & $sFileName & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    Local $sString = $outputDir & $sFileName
    For $YLoop = StringLen($sFileName) To 1 Step -1
        If StringMid($sFileName, $YLoop, 1) == "." Then
            $fl_Ext = StringMid($sFileName, $YLoop)
            $YLoop = 1
        EndIf
    Next
    If $fl_Ext == $doc Then
        Local $sfile = StringTrimRight($sString, 4) & $pdf
    ElseIf $fl_Ext == $docx Then
        Local $sfile = StringTrimRight($sString, 5) & $pdf
    EndIf
    _FileWriteLog($logfile, "$sfile : " & $sfile & @CRLF)
    _Word_DocExport($oDoc, $sfile)
    If @error <> 0 Then _FileWriteLog($logfile, @CRLF & _
            "Error exporting the document." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    _FileWriteLog($logfile, "The whole document has successfully been exported to: " & $sfile & @CRLF)
    _Word_Quit($oWord)
    FileWriteLine($procfiles, $sFileName & @CRLF)
EndFunc   ;==>WordPrint

Func SearchxlsFiles()
    _FileWriteLog($logfile, "Begin XLS Search ->" & @CRLF)
    Local $xSearch = FileFindFirstFile($inputDir & "*.xls*")
    If $xSearch = -1 Then
        Sleep(10)
        _FileWriteLog($logfile, "No Excel files found." & @CRLF)
    EndIf

    While 1
        $sFileName = FileFindNextFile($xSearch)
        If @error Then
            ExitLoop
        Else
            _FileWriteLog($logfile, "Found file : " & $sFileName & @CRLF)
            Local $iIndex = _ArraySearch($procarray, $sFileName, 0, 0, 1)
            If @error Then
                _FileWriteLog($logfile, '"' & $sFileName & '" was not processed. Converting ...' & @CRLF)
                Sleep(10)
                ExcelPrint()
            Else
                _FileWriteLog($logfile, '"' & $sFileName & '" was been processed, Skipping ...' & @CRLF)
            EndIf
        EndIf
        _FileWriteLog($logfile,"Closing EXCEL.exe" &@CRLF)
        ProcessClose("EXCEL.exe")
        ProcessWaitClose("EXCEL.exe")
    WEnd
    FileClose($xSearch)
    _FileWriteLog($logfile, "END XLS Search -> " & @CRLF)
EndFunc   ;==>SearchxlsFiles

Func ExcelPrint()
    Local $oAppl = _Excel_Open()
    If @error Then _FileWriteLog($logfile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    Global $oWorkbook = _Excel_BookOpen($oAppl, $inputDir & $sFileName)
    If @error Then
        _FileWriteLog($logfile, "Error opening workbook '" & $inputDir & $sFileName & " '." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
        _Excel_Close($oAppl)
        Exit
    EndIf
    Global $aWorkSheets = _Excel_SheetList($oWorkbook)

    If @error Then _FileWriteLog($logfile, "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    Global $aSheets = _Excel_SheetList($oWorkbook)
For $i = 0 To UBound($aSheets) - 1
    If $aSheets[$i][1].Visible <> $xlSheetVisible Then ContinueLoop
    $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($logfile, "Error setting page layout." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    $oAppl.PrintCommunication = True

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

    Local $sString = $outputDir & $sFileName
    For $YLoop = StringLen($sFileName) To 1 Step -1
        If StringMid($sFileName, $YLoop, 1) == "." Then
            $fl_Ext = StringMid($sFileName, $YLoop)
            $YLoop = 1
        EndIf
    Next
    If $fl_Ext == $xls Then
        Local $sfile = StringTrimRight($sString, 4) & $pdf
    ElseIf $fl_Ext == $xlsx Then
        Local $sfile = StringTrimRight($sString, 5) & $pdf
    EndIf
    _FileWriteLog($logfile, "$sfile : " & $sfile & @CRLF)
    ;-------------------------------------------------------------------------------------------------------------------------------------------------------
    ;FileClose($logfile)
    ;FileClose($procfiles)
    ;Exit ; HUE
    _Excel_Export($oAppl, $oWorkbook, $sfile, Default, Default, Default, Default, Default, True)
    ;-------------------------------------------------------------------------------------------------------------------------------------------------------
    If @error Then _FileWriteLog($logfile, "Error saving the workbook to '" & $sfile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF)
    _FileWriteLog($logfile, "The whole workbook was successfully exported as '" & $sfile & "'.")
    _Excel_Close($oAppl)
    FileWriteLine($procfiles, $sFileName & @CRLF)
EndFunc   ;==>ExcelPrint
;while 1
SearchdocFiles()
SearchxlsFiles()
;_FileWriteLog($logfile,"Pausing the loop for 60 seconds" & @CRLF)
;sleep(60000)
FileClose($logfile)
FileClose($procfiles)
Exit 0 ;Comment this exit if you enable the while loop above
;WEnd

Coding might look bad, but it is just one giant test 

[water]

Another idea:

Create the PDF files as big as they need to be and then use one of the many PDF splitter programs available to split the PDF into pieces.

[JustSomeone]

Another idea:

Create the PDF files as big as they need to be and then use one of the many PDF splitter programs available to split the PDF into pieces.

That's what i am going to try, but i am dissapointed and not motivated enough to make it today, might waste my time tomorow  

[water]

One of them - if not the best - is PDFtk.

[JustSomeone]

One of them - if not the best - is PDFtk.

Might use it, but i am looking more to making it use only office and autoit , just for the sake of learning things and banging my head on my desk.