Sign in to follow this  
Followers 0
strate

Printing With Excel

16 posts in this topic

$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.


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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?

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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.

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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.

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by strate

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by craig.gill

Share this post


Link to post
Share on other sites

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

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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.


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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

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