gcue Posted June 7, 2015 Share Posted June 7, 2015 hello world!I am having trouble selecting a sheet to format it. I have tried several ways but have had no luck. I tried referencing the select.items($index) among other ways. can anyone shed any light on the proper syntax i need to use?thanks in advanceexpandcollapse popup#include <Excel.au3> $msg_normal = 0 $results_xls = @ScriptDir & "\Results.xlsx" If FileExists($results_xls) Then FileDelete($results_xls) EndIf ProcessClose("excel.exe") $oAppl = _Excel_Open(False) $oWorkbook = _Excel_BookNew($oAppl, 2) _Excel_SheetAdd($oWorkbook, -1, False, 2, "Sorted|UnSorted") _Excel_SheetDelete($oWorkbook, "Sheet1") _Excel_SheetDelete($oWorkbook, "Sheet2") _Excel_RangeWrite($oWorkbook, "UnSorted", "SOURCE", "A1") _Excel_RangeWrite($oWorkbook, "UnSorted", "FILE NAME", "B1") _Excel_RangeWrite($oWorkbook, "UnSorted", "FILE EXTENSION", "C1") _Excel_RangeWrite($oWorkbook, "Sorted", "SOURCE", "A1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE NAME", "B1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE EXTENSION", "C1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE TYPE", "D1") _Excel_RangeWrite($oWorkbook, "Sorted", "MODIFIED DATE", "E1") _Excel_RangeWrite($oWorkbook, "Sorted", "TARGET", "F1") ;~ _Excel_BookClose($oWorkbook, True) ;~ $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row ;~ $oWorkbook.Sheets.Item("UnSorted").Activate ;~ $oAppl.ActiveWorkbook.Sheets(1).Select() ;~ $oAppl.Application.ScreenUpdating = False $oWorkbook.Sheets("UnSorted").Rows("1:1").Select $oWorkbook.Selection.Font.Bold = True $oWorkbook.ActiveSheet.Selection.AutoFilter $oWorkbook.ActiveSheet.Columns("A:C").AutoFit $oWorkbook.ActiveSheet.Range("A2").Select $oWorkbook.ActiveWindow.FreezePanes = True ;~ $oWorkbook.Sheets.Item("Sorted").Activate $oWorkbook.Sheets.Item("Sorted").Rows("1:1").Select $oWorkbook.Sheets.Item("Sorted").Selection.Font.Bold = True $oWorkbook.Sheets.Item("Sorted").Selection.AutoFilter $oWorkbook.Sheets.Item("Sorted").Columns("E:E").Select $oWorkbook.Sheets.Item("Sorted").Selection.NumberFormat = "mm/dd/yyyy hh:mm AM/PM" $oWorkbook.Sheets.Item("Sorted").Columns("A:F").AutoFit $oWorkbook.Sheets.Item("Sorted").Range("A2").Select $oWorkbook.Sheets.Item("Sorted").ActiveWindow.FreezePanes = True ;~ $oAppl.Application.ScreenUpdating = True _Excel_BookSaveAs($oWorkbook, $results_xls) _Excel_BookClose($oAppl) ProcessClose("excel.exe") ShellExecute($results_xls) Link to comment Share on other sites More sharing options...
water Posted June 7, 2015 Share Posted June 7, 2015 I wouldn't work with Selections (that's the visible part as if the user had selected a few cells) but with Ranges. That's faster and easier.You could have a look in the wiki to see how ranges can get formatted.BTW: Why do you run ProcessClose? That's a pretty brute method of closing Excel. Simply use _Excel_Open/_Excel_Close and let the UDF do all the work. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 7, 2015 Author Share Posted June 7, 2015 the need for processclose used to exist from an older version of the udf - the process wouldnt close out clean most of the time. i just have been carrying it forward to be safe. ill test without it.got most of it working except the freezepanes tried a few things but cant seem to get it working..thanks for your help! expandcollapse popup#include <Excel.au3> $msg_normal = 0 $results_xls = @ScriptDir & "\Results.xlsx" If FileExists($results_xls) Then FileDelete($results_xls) EndIf ProcessClose("excel.exe") $oAppl = _Excel_Open(False) $oWorkbook = _Excel_BookNew($oAppl, 2) _Excel_SheetAdd($oWorkbook, -1, False, 2, "Sorted|UnSorted") _Excel_SheetDelete($oWorkbook, "Sheet1") _Excel_SheetDelete($oWorkbook, "Sheet2") _Excel_RangeWrite($oWorkbook, "UnSorted", "SOURCE", "A1") _Excel_RangeWrite($oWorkbook, "UnSorted", "FILE NAME", "B1") _Excel_RangeWrite($oWorkbook, "UnSorted", "FILE EXTENSION", "C1") _Excel_RangeWrite($oWorkbook, "Sorted", "SOURCE", "A1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE NAME", "B1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE EXTENSION", "C1") _Excel_RangeWrite($oWorkbook, "Sorted", "FILE TYPE", "D1") _Excel_RangeWrite($oWorkbook, "Sorted", "MODIFIED DATE", "E1") _Excel_RangeWrite($oWorkbook, "Sorted", "TARGET", "F1") $oWorkbook.Sheets("UnSorted").Activate $oRange = $oWorkBook.ActiveSheet.Range("A1").EntireRow $oRange.Font.Bold = True $oRange.AutoFilter $oRange.Columns("A:C").AutoFit $oWorkBook.ActiveSheet.Range("A2").Select $oWorkbook.ActiveWindow.FreezePanes = True $oWorkbook.Sheets("Sorted").Activate $oRange = $oWorkBook.Activesheet.Range("E1").EntireColumn $oRange.NumberFormat = "mm/dd/yyyy hh:mm AM/PM" $oRange = $oWorkBook.ActiveSheet.Range("A1").EntireRow $oRange.Font.Bold = True $oRange.AutoFilter $oRange.Columns("A:F").AutoFit $oWorkBook.ActiveSheet.Range("A2").Select $oWorkbook.ActiveWindow.FreezePanes = True _Excel_BookSaveAs($oWorkbook, $results_xls) _Excel_BookClose($oAppl) ShellExecute($results_xls) Link to comment Share on other sites More sharing options...
water Posted June 7, 2015 Share Posted June 7, 2015 ActiveWindow is a property of the application object not the workbook. Hence it should be:$oExcel.ActiveWindow.FreezePanes = True My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted June 7, 2015 Share Posted June 7, 2015 BTW:What is ShellExecute for? It's just wasting processing time when you simply want to display the resulting workbook.Try:_Excel_BookSaveAs($oWorkbook, $results_xls) $oExcel.Visible = True My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 ActiveWindow is a property of the application object not the workbook. Hence it should be:$oExcel.ActiveWindow.FreezePanes = True makes sense - thank you!!!! Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 BTW:What is ShellExecute for? It's just wasting processing time when you simply want to display the resulting workbook.Try:_Excel_BookSaveAs($oWorkbook, $results_xls) $oExcel.Visible = Truethanks! - that definitely seems to work better. i have a question, do i need to have the user exit excel before i start creating/writing to a file through the UDF? the old UDF had issues with that - excel writes would appear even though id specify invisible and would interrupt the user's use of excel. Link to comment Share on other sites More sharing options...
water Posted June 8, 2015 Share Posted June 8, 2015 It's causing problems when the user edits a cell and you want to write to the Workbook at the same time.You could call _Excel_Open with parameter $bInteractive set to False. So mouse/keyboard are blocked for the user unstil released by you. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 i think id rather just tell the user to exit excel before hand. blocking mouse/kb is even more intrusive i think. Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 wait - do you mean editing the cell of the same workbook? or any workbook? Link to comment Share on other sites More sharing options...
water Posted June 8, 2015 Share Posted June 8, 2015 I think "same Workbook".If you like you can tell Excel to open a new instance, run it inivisible and under your full control without any possible way for the user to interfere. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 how do you open a new instance seperate from what the user is using? Link to comment Share on other sites More sharing options...
Jewtus Posted June 8, 2015 Share Posted June 8, 2015 I think you can use $oApp2 = _Excel_Open(False) Link to comment Share on other sites More sharing options...
water Posted June 8, 2015 Share Posted June 8, 2015 _Excel_Open(False, False, False, False, True)Forces the UDF to open a new instance. This instance is invisible, does no screen updating, blocks all user input. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 weird.. that didnt open the excel file for me. instead i got 2 grey screen instances of excel. Link to comment Share on other sites More sharing options...
water Posted June 8, 2015 Share Posted June 8, 2015 This works fine here (Excel 2010 on Windows 7):#include <Excel.au3> $oExcel = _Excel_Open(False, False, False, False, True) ConsoleWrite(@error & @CRLF) $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx") ConsoleWrite(@error & @CRLF) _Excel_Close($oExcel) ConsoleWrite(@error & @CRLF) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 (edited) im doing this... also using win 7 excel 2010 $oExcel = _Excel_Open(False, False, False, False, True) $oWorkbook = _Excel_BookNew($oExcel, 2) _Excel_rangewrites _Excel_BookSaveAs($oWorkbook, $results_xls) $oExcel.Visible = True Edited June 8, 2015 by gcue Link to comment Share on other sites More sharing options...
water Posted June 8, 2015 Share Posted June 8, 2015 (edited) Insert$oExcel.ScreenUpdating = True $oExcel.Interactive = Truebefore$oExcel.Visible = True Edited June 8, 2015 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 that seems to work much betterthank you thank you!!!! Link to comment Share on other sites More sharing options...
gcue Posted June 8, 2015 Author Share Posted June 8, 2015 i think something else to keep in mind along the lines of having excel open... is to check to see if the file is already openrunning this first.$results_xls = @ScriptDir & "\Results.xlsx" $file_usage = FileOpen($results_xls, 1) If $file_usage = -1 Then MsgBox($msg_error, $script_name, $results_xls & " is in use." & @CRLF & _ @CRLF & _ 'Please close it before continuing.') Return EndIf FileClose($file_usage) If FileExists($results_xls) Then FileDelete($results_xls) EndIf 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