Sign in to follow this  
Followers 0
JustSomeone

Convert word&excel files to PDF

5 posts in this topic

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.

#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 

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. :)

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  
Followers 0