strate Posted February 24, 2006 Share Posted February 24, 2006 $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 EndIfI'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... Link to comment Share on other sites More sharing options...
randallc Posted February 24, 2006 Share Posted February 24, 2006 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
strate Posted February 24, 2006 Author Share Posted February 24, 2006 Hi,$oexcel, then "myExcel"?....Best, randallThank 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... Link to comment Share on other sites More sharing options...
randallc Posted February 25, 2006 Share Posted February 25, 2006 (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 questionsMacroRunfind the appropriate commands and how they workAutoIT in Excel comBest, randallPS 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
strate Posted February 25, 2006 Author Share Posted February 25, 2006 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 questionsMacroRunfind the appropriate commands and how they workAutoIT in Excel comBest, randallPS 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... Link to comment Share on other sites More sharing options...
strate Posted February 27, 2006 Author Share Posted February 27, 2006 On some of the workbooks I have to print excel creates a pop up that statesThis 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... Link to comment Share on other sites More sharing options...
skippynz Posted February 27, 2006 Share Posted February 27, 2006 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 Link to comment Share on other sites More sharing options...
strate Posted February 27, 2006 Author Share Posted February 27, 2006 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 EndFuncI 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... Link to comment Share on other sites More sharing options...
strate Posted February 27, 2006 Author Share Posted February 27, 2006 (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 February 27, 2006 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... Link to comment Share on other sites More sharing options...
skippynz Posted February 28, 2006 Share Posted February 28, 2006 (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 February 28, 2006 by craig.gill Link to comment Share on other sites More sharing options...
flyingboz Posted February 28, 2006 Share Posted February 28, 2006 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 ) Reading the help file before you post... Not only will it make you look smarter, it will make you smarter. Link to comment Share on other sites More sharing options...
randallc Posted February 28, 2006 Share Posted February 28, 2006 (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 February 28, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
strate Posted February 28, 2006 Author Share Posted February 28, 2006 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... Link to comment Share on other sites More sharing options...
randallc Posted February 28, 2006 Share Posted February 28, 2006 (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,"") exitBest, Randall Edited February 28, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
strate Posted March 1, 2006 Author Share Posted March 1, 2006 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... Link to comment Share on other sites More sharing options...
randallc Posted March 1, 2006 Share Posted March 1, 2006 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now