Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

And more Options:

Note: only one scaling option should be TRUE

<!-- : Begin batch script
@ECHO OFF
CLS

cscript //nologo "%~f0?.wsf" "FILEPATH=C:\tmp\Book1.xlsx" "EXCELSHEET=Sheet1" "ORIENTATION=LandScape" "COPIES=2" "DUPLEXSENDKEYS=TRUe" "DUPLEXFLIPON=Short" "FitSheetonOnePage=" "FitAllColumnsOnOnePage=" "FitAllRowsOnOnePage=True" //job:EXCEL
exit /b
PAUSE
----- Begin wsf script --->
<package>
  <job id="EXCEL">
    <script language="VBScript">
        Dim ObjArgs
        Set ObjArgs = wscript.arguments

        Dim objShell
        Set objShell = WScript.CreateObject ("WScript.shell") 
        
        Dim FitSheetonOnePage
        FitSheetonOnePage = Null
        Dim FitAllColumnsOnOnePage
        FitAllColumnsOnOnePage = Null
        Dim FitAllRowsOnOnePage
        FitAllRowsOnOnePage = Null
        Dim DUPLEXSENDKEYS
        DUPLEXSENDKEYS= Null 
        Dim DUPLEXFLIPON
        DUPLEXFLIPON = Null
        Dim COPIES
        COPIES = Null
        Dim ORIENTATION
        ORIENTATION = Null
        Dim EXCELSHEET
        EXCELSHEET = Null

        Dim objDictionary
        Set objDictionary = CreateObject("Scripting.Dictionary")
        For I = 0 to objArgs.Count-1
        objDictionary.CompareMode = vbTextCompare
        objDictionary.Add Split(objArgs(I), "=")(0), Split(objArgs(I), "=")(1)
        Next

        'Open Document
        Dim XLApp
        Dim XLWkbk
        Set XLApp= CreateObject("Excel.Application")
        DUPLEXSENDKEYS = StrComp(ObjDictionary("DUPLEXSENDKEYS"), "TRUE", 1)
        DUPLEXFLIPON = StrComp(ObjDictionary("DUPLEXFLIPON"), "SHORT", 1)
        FitSheetonOnePage = StrComp(ObjDictionary("FitSheetonOnePage"), "TRUE", 1)
        FitAllColumnsOnOnePage = StrComp(ObjDictionary("FitAllColumnsOnOnePage"), "TRUE", 1)
        FitAllRowsOnOnePage = StrComp(ObjDictionary("FitAllRowsOnOnePage"), "TRUE", 1)
        'https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
        '.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
        If DUPLEXSENDKEYS = 0 OR DUPLEXFLIPON = 0 OR FitSheetonOnePage = 0 OR FitAllColumnsOnOnePage = 0 OR FitAllRowsOnOnePage = 0 Then
         XLApp.Visible = True
         Set XLWkbk = XLApp.Workbooks.Open(objDictionary("FILEPATH"), ,TRUE)
        ELSE
         XLApp.Visible = False
         Set XLWkbk = XLApp.Workbooks.Open(objDictionary("FILEPATH"), ,TRUE)
        END IF

        'Print Specific WS?
        Dim ws
        If Not objDictionary("EXCELSHEET") = "" Then
         Set ws = XLWkbk.Sheets(objDictionary("EXCELSHEET"))
        ELSE
         Set ws = XLWkbk.Worksheets(1)
        End If

        'Set Orientation
        ORIENTATION = StrComp(objDictionary("ORIENTATION"), "LANDSCAPE", 1)
        If ORIENTATION = 0 Then
         ws.PageSetup.Orientation = 2
        End If
        ORIENTATION = StrComp(objDictionary("ORIENTATION"), "Portrait", 1)
        If ORIENTATION = 0 Then
         ws.PageSetup.Orientation = 1
        End If

        'Duplex Settings
        WScript.Sleep 1000         
        ObjShell.AppActivate "Excel"
        WScript.Sleep 1000   
        If DUPLEXSENDKEYS = 0 Then 
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "^p", TRUE ' CTRL + P [Print Screen]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%p", TRUE ' Alt + P [Print Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%d", TRUE ' Alt + d [Print Duplex Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{DOWN}", TRUE ' Down [Print Duplex Default Long]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{ENTER}", TRUE
        End If
        If DUPLEXFLIPON = 0 Then
          WScript.Sleep 500       
          ObjShell.SENDKEYS "^p", TRUE ' CTRL + P [Print Screen]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%p", TRUE ' Alt + P [Print Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%d", TRUE ' Alt + d [Print Duplex Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{DOWN}", TRUE ' Down [Print Duplex Short] 
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{ENTER}", TRUE
        End If

        'Scaling
        If FitSheetonOnePage = 0 OR FitAllColumnsOnOnePage = 0 OR FitAllRowsOnOnePage = 0 THEN
         'Reset back to Default
          WScript.Sleep 500       
          ObjShell.SENDKEYS "^p", TRUE ' CTRL + P [Print Screen]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%p", TRUE ' Alt + P [Print Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%g", TRUE ' Alt + d [Page Setup]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%a", TRUE ' Alt + d [AutoFit]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{ENTER}", TRUE
          WScript.Sleep 500       
          ObjShell.SENDKEYS "^p", TRUE ' CTRL + P [Print Screen]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%p", TRUE ' Alt + P [Print Options]
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "%s", TRUE ' Alt + d [Print Scaling Options]
          WScript.Sleep 1000
         End If 
         If FitSheetonOnePage = 0 Then  
          WScript.Sleep 1000      
          ObjShell.SENDKEYS "{DOWN}", TRUE ' Down x1 [FitSheetonOnePage] 
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{ENTER}", TRUE
         End If
         If FitAllColumnsOnOnePage = 0 Then  
          WScript.Sleep 500                
          ObjShell.SENDKEYS "{DOWN}{DOWN}", TRUE ' Down x2 [FitAllColumnsOnOnePage] 
          WScript.Sleep 1000       
          ObjShell.SENDKEYS "{ENTER}", TRUE
         End If
         If FitAllRowsOnOnePage = 0 Then  
          WScript.Sleep 500           
          ObjShell.SENDKEYS "{DOWN}{DOWN}{DOWN}", TRUE ' Down x3 [FitAllRowsOnOnePage] 
          WScript.Sleep 1000            
          ObjShell.SENDKEYS "{ENTER}", TRUE
         End If

        'PrintOut     
        '.PrintOut (From, To, Copies, Preview[Bool], ActivePrinter, PrintToFile, Collate[Bool], PrToFileName[Bool], IgnorePrintAreas[Bool])
        If Not objDictionary("COPIES") = "" Then
         Call ws.PrintOut( , ,objDictionary("COPIES"), , , ,TRUE)
        ELSE
         Call ws.PrintOut( , , , , , ,TRUE)
        End If

        XLWkbk.Close False
        XLApp.Quit

	Dim FPath
        TEMP=ObjShell.ExpandEnvironmentStrings("%TEMP%")
        FPath = TEMP & "\jobrunning.log"
        Dim ObjFS
        Set ObjFS = CreateObject("Scripting.FileSystemObject")
        IF ObjFS.FileExists(FPath) THEN
            ObjFS.DeleteFile FPath
        END IF

        Set FPath = Nothing
        Set ObjFS = Nothing
        Set ws = Nothing
        Set XLWkbk = Nothing
        Set XLApp = Nothing
        Set ObjArgs = Nothing
        Set objDictionary = Nothing
        Set ObjShell = Nothing
    </script>
  </job>
</package>


https://pisquare.osisoft.com/thread/1809

https://stackoverflow.com/questions/46901160/how-to-print-excel-by-command-line

Hyrbid Solutiion:

<!-- : Begin batch script
@ECHO OFF
CLS

cscript //nologo "%~f0?.wsf" "FILEPATH=C:\tmp\Book1.xlsx" "EXCELSHEET=Sheet2" "ORIENTATION=LandScape" "COPIES=2" //job:EXCEL
exit /b
PAUSE
----- Begin wsf script --->
<package>
  <job id="EXCEL">
    <script language="VBScript">
        Dim ObjArgs
        Set ObjArgs = wscript.arguments

        Dim COPIES
        COPIES = Null
        Dim ORIENTATION
        ORIENTATION = Null
        Dim EXCELSHEET
        EXCELSHEET = Null

        Dim objDictionary
        Set objDictionary = CreateObject("Scripting.Dictionary")
        For I = 0 to objArgs.Count-1
        objDictionary.CompareMode = vbTextCompare
        objDictionary.Add Split(objArgs(I), "=")(0), Split(objArgs(I), "=")(1)
        Next

        Dim XLApp
        Dim XLWkbk
        Set XLApp= CreateObject("Excel.Application")
        XLApp.Visible = False
        Set XLWkbk = XLApp.Workbooks.Open(objDictionary("FILEPATH"))
        Dim ws
        If Not objDictionary("EXCELSHEET") = "" Then
         Set ws = XLWkbk.Sheets(objDictionary("EXCELSHEET"))
        ELSE
         Set ws = XLWkbk.Worksheets(1)
        End If
        ORIENTATION = StrComp(objDictionary("ORIENTATION"), "LANDSCAPE", 1)
        If ORIENTATION = 0 Then
         ws.PageSetup.Orientation = 2
        End If
        ORIENTATION = StrComp(objDictionary("ORIENTATION"), "Portrait", 1)
        If ORIENTATION = 0 Then
         ws.PageSetup.Orientation = 1
        End If        
        Dim ObjWS
        '.PrintOut (From, To, Copies, Preview[Bool], ActivePrinter, PrintToFile, Collate[Bool], PrToFileName[Bool], IgnorePrintAreas[Bool])
        If Not objDictionary("COPIES") = "" Then
         ObjWS = ws.PrintOut( , ,objDictionary("COPIES"), , , ,TRUE)
        ELSE
         ObjWS = ws.PrintOut( , , , , , ,TRUE)
        End If

        XLWkbk.Close False
        XLApp.Quit

	Dim FPath
        TEMP=ObjShell.ExpandEnvironmentStrings("%TEMP%")
        FPath = TEMP & "\jobrunning.log"
        Dim ObjFS
        Set ObjFS = CreateObject("Scripting.FileSystemObject")
        IF ObjFS.FileExists(FPath) THEN
            ObjFS.DeleteFile FPath
        END IF

        Set FPath = Nothing
        Set ObjFS = Nothing
        Set ObjWS = Nothing
        Set ws = Nothing
        Set XLWkbk = Nothing
        Set XLApp = Nothing
        Set ObjArgs = Nothing
        Set objDictionary = Nothing
        Set ObjShell = Nothing
    </script>
  </job>
</package>

Note: There is no print switch to excel.exe (there is for word and powerpoint...wtf Microsoft....)

https://bettersolutions.com/excel/workbooks/startup-switches.htm

/safemodeOpens Excel ignoring all the Startup Folders and All Add-ins, including VBA Add-ins, Automation Add-ins and COM Add-ins.
This is the same as holding down the Ctrl key when you start Excel.
/safeSame as above
/sSame as above
/a progidOpens Excel and loads the Automation Add-in with the corresponding ProgID.
excel.exe /a MyProgID.Name
/automationOpens Excel ignoring all the Startup Folders and any VBA Add-ins.
/embeddedOpens Excel in Embedded mode which prevents the default workbook Book.xlt from being created.
/embedSame as above
/eSame as above
/m(Redundant) Opens Excel with a new workbook containing a single Excel 4.0 macro sheet.
/n filepathOpens Excel using a specified file as a template for the default workbook.
excel.exe /n "c:\temp\MyBook.xlsx"
/oOpens Excel forcing it to re-register itself in the Window Registry.
/p folderpathOpens Excel overriding the default local file location user option with a new folder path.
/r filepathOpens Excel with a specific file in read-only mode. You must include the path and filename afterwards.
/t filepathOpens Excel using a specified file as a template for the default workbook.
excel.exe /t http://mysite.com/MyBook.xlsx
/xStarts a new instance of Excel as a separate process.
/regserverForces Excel to re-register itself in the Windows Registry and then quit.
/unregserverForces Excel to unregister itself in the Windows Registry and then quit.
filepath (no switch)Starts Excel and opens a specific file.
This parameter does not require a switch
excel.exe "c:\temp\MyBook.xlsx"
  • No labels