Printing With Excel

[strate]

$File_List = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)',4)
$MyExcel = ObjCreate("Excel.Application"); Create an Excel Object
if @error then 
  Msgbox (0,"","Error creating Excel object. Error code: " & @error)
  exit
endif
if not IsObj($MyExcel) then 
  Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.")
  exit
endif

$Strings = StringSplit($File_List,'|')
If $strings[0] > 1 Then
;~  For $i = 2 To $Strings[0] + 1
;~      MsgBox(262144,'Debug line ~17','Selection:' & @lf & '$Strings[0] - 1' & @lf & @lf & 'Return:' & @lf & $Strings[0] - 1 & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX
;~      MsgBox(262144,'Debug line ~19','Selection:' & @lf & '$Strings[1]&''\''&$Strings[$i]' & @lf & @lf & 'Return:' & @lf & $Strings[1]&'\'&$Strings[$i] & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX      
;~      MsgBox(262144,'Debug line ~19','Selection:' & @lf & '$Strings[$i]' & @lf & @lf & 'Return:' & @lf & $Strings[$i] & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX
;~      MsgBox(262144,'Debug line ~19','Selection:' & @lf & '$i' & @lf & @lf & 'Return:' & @lf & $i & @lf & @lf & '@Error:' & @lf & @Error);### Debug MSGBOX
;~      $oExcel = ObjGet($Strings[1]&'\'&$Strings[$i],"Excel.Application")
;~      $oExcel.Windows(1).Visible = 1
;~      $MyExcel.Visible = 1
;~      $MyExcel.PrintOut = 1
;~  Next
Else
    $oExcel = ObjGet($Strings[1],"Excel.Application")
    $oExcel.Windows(1).Visible = 1
    $MyExcel.Visible = 1
    $MyExcel.PrintOut
EndIf

I'm trying to print a single work sheet with excel. Why am I having problems with this? I searched the forums for PrintOut and nothing was usefull as an example. Help would be great.

[randallc]

Hi,

$oexcel,

then "myExcel"?....

$FilePath=@ScriptDir&"\book1.xls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

$FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

$oExcel = ObjGet($FilePath)

$oExcel.Windows(1).Visible = 1

$oExcel.Application.activesheet.Visible = 1

$oExcel.Application.activesheet.PrintOut()

Best, randall

[strate]

Hi,

$oexcel,

then "myExcel"?....

Best, randall

Thank you very much, those came from trying to piece it together from the Excel UDF. What can I look at to learn how COM is wrote? Or how to execute a task?

[randallc]

Hi,

Here are some links to how we have tried to work out excel probs in the past!

Good luck!

automating excel charts com, Syntax / Object questions

MacroRun

find the appropriate commands and how they work

AutoIT in Excel com

Best, randall

PS try the excel group at Microsoft fopr specific questions; VBA help in excel usually gives the commands with brackets and sequenceorder you'll need.

The problem often is working out whether you need ".Application.Sheet1" etc in your commmand.

Edited February 25, 2006 by randallc

[strate]

Hi,

Here are some links to how we have tried to work out excel probs in the past!

Good luck!

automating excel charts com, Syntax / Object questions

MacroRun

find the appropriate commands and how they work

AutoIT in Excel com

Best, randall

PS try the excel group at Microsoft fopr specific questions; VBA help in excel usually gives the commands with brackets and sequenceorder you'll need.

The problem often is working out whether you need ".Application.Sheet1" etc in your commmand.

Thanks a ton, we live in excel at work and I hate using it so I appreciate this immensly.

[strate]

On some of the workbooks I have to print excel creates a pop up that states

This workbook contains links to other data sources.

- To update all linked information, click Yes.

- To keep the existing information, click No.

Is it possible to get it to answer this for me? The window doesn't always pop up so I can't simply winwait for it.

[skippynz]

On some of the workbooks I have to print excel creates a pop up that states

Is it possible to get it to answer this for me? The window doesn't always pop up so I can't simply winwait for it.

cant you use the adlib section so that it will check for the message box and answer it, that way you dont need a winwait. as in below

AdlibEnable("myadlib")
;...
Exit

Func myadlib()
    If WinActive("Error") Then
       ;...
    EndIf
EndFunc

[strate]

cant you use the adlib section so that it will check for the message box and answer it, that way you dont need a winwait. as in below

AdlibEnable("myadlib")
;...
Exit

Func myadlib()
    If WinActive("Error") Then
      ;...
    EndIf
EndFunc

I never read that before, that will help me in other scripts also thanks alot.

[strate]

Here's my code so far....

Basically completed by randallc.

$File_List = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)',4)

$Strings = StringSplit($File_List,'|')
If $strings[0] > 1 Then
    For $i = 2 To $Strings[0]
        $oExcel = ObjGet($Strings[1]&'\'&$Strings[$i])
        $oExcel.Windows(1).Visible = 1
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.activesheet.Visible = 1
        $oExcel.Application.activesheet.PrintOut()
        ProcessClose('EXCEL.EXE')
    Next
Else
    $oExcel = ObjGet($Strings[1])
    $oExcel.Windows(1).Visible = 1
    $oExcel.Application.DisplayAlerts = 0
    $oExcel.Application.activesheet.Visible = 1
    $oExcel.Application.activesheet.PrintOut()
                ProcessClose('EXCEL.EXE')
EndIf

When it prints it prints the last item selected to the first item selected. How do I switch the strings around so that it prints in the order they are selected?

Edited February 27, 2006 by strate

[skippynz]

Here's my code so far....

Basically completed by randallc.

$File_List = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)',4)

$Strings = StringSplit($File_List,'|')
If $strings[0] > 1 Then
    For $i = 2 To $Strings[0]
        $oExcel = ObjGet($Strings[1]&'\'&$Strings[$i])
        $oExcel.Windows(1).Visible = 1
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.activesheet.Visible = 1
        $oExcel.Application.activesheet.PrintOut()
        ProcessClose('EXCEL.EXE')
    Next
Else
    $oExcel = ObjGet($Strings[1])
    $oExcel.Windows(1).Visible = 1
    $oExcel.Application.DisplayAlerts = 0
    $oExcel.Application.activesheet.Visible = 1
    $oExcel.Application.activesheet.PrintOut()
                ProcessClose('EXCEL.EXE')
EndIf

When it prints it prints the last item selected to the first item selected. How do I switch the strings around so that it prints in the order they are selected?

i think thats got something to do with the way you selected the files. ie the last one could be the first one in the array ? maybe ? not totally sure on that though. do the following before you split out the string to display whats in the array so you can check the order.

#include <Array.au3>
_ArrayDisplay ( $Strings, "title of window here")

Edited February 28, 2006 by craig.gill

[flyingboz]

ProcessClose('EXCEL.EXE')

You really ought to replace that with the .Quit method, much more graceful, (and you know which copy of excel you're killing )

[randallc]

Hi,

I think you may need to change a little, and creatobject, then open, with these parms;[vba excel help...]

Opens a workbook.

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

expression Required. An expression that returns the Workbooks object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning

0 Doesn't update any references

1 Updates external references but not remote references

2 Updates remote references but not external references

3 Updates both remote and external references

;XLprintEx.au3

#include<ExcelCom.au3>

$FilePath=@ScriptDir&"\book1.xls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

$FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

;============================================

$ExcelMain=ObjCreate("Excel.Application")

$ExcelMain.Visible = 1

;============================================

; while;in your loops

$ExcelMain.Workbooks.open($FilePath,0)

$oExcel = ObjGet($FilePath)

;$oExcel.Application.activesheet.Visible = 1; not needed for print?? - not sure

_XLshow($FilePath,1);[just in the example....]

MsgBox(0,"","Here we go... now print")

;$oExcel.Application.activesheet.PrintOut()

;============================================after print

$oExcel.close(0)

; wend

;============================================end of the loops, finishing script

$ExcelMain.quit

$ExcelMain=""

Best, Randall

[PS does "_ArrayReverse" help yoiu switch the $ar_Strings array to correct order?]

Edited February 28, 2006 by randallc

[strate]

When the pages are printed it puts them into numerical order, instead of the order they were clicked is this changeable?

[randallc]

Hi,

Maybe select them one at a time;

#include<Array.au3>

global $Strings[1],$error

while $error<>1

$File_Sel = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)')

$error=@error

redim $Strings[ubound($Strings)+1]

$Strings [ubound($Strings)-1]=$File_Sel

wend

_ArrayDelete($Strings,ubound($Strings))

$Strings [0]=ubound($Strings)

_ArrayDisplay($Strings,"")

exit

Best, Randall Edited February 28, 2006 by randallc

[strate]

This is gonna sound bad but how can I get your most recent hlep to work with the other help you gave me?

Here's the old code:

#include<ExcelCom.au3>
$File_List = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)',4)
;~ WinWait('Print Process Sheets')
;~ WinActivate('Print Process Sheets')
;~ WinWaitActive('Print Process Sheets')
;~ Sleep(1000)
;~ ControlSetText('Print Process Sheets','','Button2', 'Print' )
If $File_List = '' Then Exit
BlockInput(1)
$Strings = StringSplit($File_List,'|')
$ExcelMain=ObjCreate("Excel.Application")
$ExcelMain.Visible = 1
If $strings[0] > 1 Then
    For $i = 2 To $Strings[0]
        $FilePath=$Strings[1]&'\'&$Strings[$i]
        $ExcelMain.Workbooks.open($FilePath,0)
        $oExcel = ObjGet($FilePath)
        _XLshow($FilePath,1);[just in the example....]
        $oExcel.Application.activesheet.PrintOut()
        $oExcel.close(0)
    Next
Else
    $FilePath=$Strings[1]
    If Not StringInStr($FilePath,'.xls') Then
        $FilePath = $FilePath & '.xls'
    EndIf
    $ExcelMain.Workbooks.open($FilePath,0)
    $oExcel = ObjGet($FilePath)
    _XLshow($FilePath,1);[just in the example....]
    $oExcel.Application.activesheet.PrintOut()
    $oExcel.close(0)
EndIf
$ExcelMain.quit
$ExcelMain=""
BlockInput(0)

I had to change your other code so that it would add the file extension for me.

#include<Array.au3>
global $Strings[1],$error
while $error<>1
    $File_Sel = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)')
    $error=@error
    If Not StringInStr($File_Sel,'.xls') Then
        $File_Sel = $File_Sel & '.xls'
    EndIf
    redim $Strings[ubound($Strings)+1]
    $Strings [ubound($Strings)-1]=$File_Sel
wend
_ArrayDelete($Strings,ubound($Strings))
$Strings [0]=ubound($Strings)
_ArrayDisplay($Strings,"")
exit

How do I merge the two? The _ArrayDisplay is screwing me up.

[randallc]

Hi,

change to print, and remove show and msgbox;

;XLFileList2.au3

#include<ExcelCom.au3>

#include<Array.au3>

global $Strings[1],$error

while $error<>1

$File_Sel = FileOpenDialog('Print Process Sheets','G:\Packaging\Process Sheets','Excel Files (*.xls)')

$error=@error

If Not StringInStr($File_Sel,'.xls') Then

$File_Sel = $File_Sel & '.xls'

EndIf

redim $Strings[ubound($Strings)+1]

$Strings [ubound($Strings)-1]=$File_Sel

wend

_ArrayDelete($Strings,ubound($Strings))

$Strings [0]=ubound($Strings)

;don't need to display the array unless you want to check; starts at number1

;BlockInput(1)

$ExcelMain=ObjCreate("Excel.Application")

$ExcelMain.Visible = 1

If $strings[0] > 1 Then

For $i = 1 To $Strings[0]-1

$FilePath=$Strings[$i]

$ExcelMain.Workbooks.open($FilePath,0)

$oExcel = ObjGet($FilePath)

_XLshow($FilePath,1);[just in the example....]

MsgBox(0,"","hi")

;$oExcel.Application.activesheet.PrintOut()

$oExcel.close(0)

Next

EndIf

$ExcelMain.quit

$ExcelMain=""

BlockInput(0)

Best, Randall