water Posted July 14, 2013 Author Share Posted July 14, 2013 Give function _Excel_BookOpenText a try. 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...
nitekram Posted July 14, 2013 Share Posted July 14, 2013 (edited) @waterLooking at it now, thanks you.What about importing an Array?edit think I found it? _Excel_RangeWrite() Edited July 14, 2013 by nitekram 2¢ All by me:"Sometimes you have to go back to where you started, to get to where you want to go." "Everybody catches up with everyone, eventually" "As you teach others, you are really teaching yourself." From my dad "Do not worry about yesterday, as the only thing that you can control is tomorrow." WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2 AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit Docs SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language Programming Tips Excel Changes ControlHover.UDF GDI_Plus Draw_On_Screen GDI Basics GDI_More_Basics GDI Rotate GDI Graph GDI CheckExistingItems GDI Trajectory Replace $ghGDIPDll with $__g_hGDIPDll DLL 101? Array via Object GDI Swimlane GDI Plus French 101 Site GDI Examples UEZ GDI Basic Clock GDI Detection Ternary operator Link to comment Share on other sites More sharing options...
water Posted July 14, 2013 Author Share Posted July 14, 2013 Exactly, _Excel_RangeWrite is the function you need. 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...
apoliemans Posted July 21, 2013 Share Posted July 21, 2013 In ExcelConstants.au3, please include the XlConsolidationFunction Enumeration constants as described here. http://msdn.microsoft.com/en-us/library/office/ff837374.aspx They are used when configuring fields in PivotTables. Andre Link to comment Share on other sites More sharing options...
water Posted July 21, 2013 Author Share Posted July 21, 2013 Andre, ExcelConstants only contains those enumerations used by a function of the UDF or those a user can pass as a parameter. Until the UDF contains a pivot function please add the constant directly to your script. 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...
apoliemans Posted July 21, 2013 Share Posted July 21, 2013 @water, I see, thanks for explaining the procedure. Link to comment Share on other sites More sharing options...
water Posted July 26, 2013 Author Share Posted July 26, 2013 Released Alpha 7. For download and a history of changes please see post #1. 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 August 4, 2013 Author Share Posted August 4, 2013 Released Alpha 8.For download and a history of changes please see post #1. 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 August 15, 2013 Author Share Posted August 15, 2013 Released Beta 1.For download and a history of changes please see post #1. 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 August 20, 2013 Author Share Posted August 20, 2013 The rewrite of the Excel UDF has now reached the Beta state. What is still missing? Basic formatting functions (font (size, color, italic ..), background color, borders ...) Filter functions: Add filter, remove (all) filters, get information about filters ...? Please tell me what you want to see in the next beta! 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...
Spiff59 Posted August 21, 2013 Share Posted August 21, 2013 (edited) For a few years anytime I'd install a new version of AutoIt (Prod or Beta) I'd immediately go delete three functions from the Excel UDF: _ExcelBookOpen(), _ExcelSheetAddNew(), and _ExcelReadSheetToArray(). Bugtracker #2199 fixed the issue with _ExcelSheetAddNew(). Bugtracker #2219 had the rewrite of _ExcelReadSheetToArray(). Both were implemented in Beta version 3.3.9.5. I'm now left with only having to replace a single function, the standard _ExcelBookOpen(), with a custom version because it still has no logic to report whether a worksheet that was requested to be opened in read-write mode, was instead opened as read-only. There was some discussion in this thread about _ExcelBookOpen around posts #7 and #43. Shortly after post #43 I modified the function again to use the .Readonly property instead of the FileOpen/FileClose method and have been using the following ever since: Global Const $xlSheetVisible = -1 _ExcelBookOpen2("\\Server1\Directory1\test.xls", True, False) If @error Then MsgBox(0,"","@Error = " & @error) ;----------------------------------------------------------------------------------------------------------------------------------- Func _ExcelBookOpen2($sFilePath, $fVisible = True, $fReadOnly = False, $sPassword = "", $sWritePassword = "") If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) $fVisible = ($fVisible > 0) $fReadOnly = ($fReadOnly > 0) Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) Local $oWorkbook = $oExcel.WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword) If (Not $fReadOnly) And $oWorkbook.Readonly Then $oExcel.Quit Return SetError(3, 0, 0) EndIf $oExcel.Visible = $fVisible ; Select the first visible worksheet. For $i = 1 To $oWorkbook.Sheets.Count If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then $oWorkbook.Sheets($i).Select() ExitLoop EndIf Next Return $oExcel EndFunc ;==>_ExcelBookOpen It puts the "@Error = 2 - File not found" test where it ought to be (before launching Excel), simplifies the parameter edits, eliminates a flock of truly unnecessaary compound If/Then statements, and adds an "@Error = 3 - Workbook in use" test to report when a workbook is not available for update. Edited August 21, 2013 by Spiff59 Link to comment Share on other sites More sharing options...
water Posted August 21, 2013 Author Share Posted August 21, 2013 That's implemented in Excel Rewrite too. Another question: Do you know why it is sensible to search for the first visible Worksheet when opening a Workbook? ; Select the first visible worksheet. For $i = 1 To $oWorkbook.Sheets.Count If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then $oWorkbook.Sheets($i).Select() ExitLoop EndIf Next 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...
Spiff59 Posted August 23, 2013 Share Posted August 23, 2013 (edited) I'd thought those changes had been implemented. When the AutoIt beta 17 forced me to rename _ExcelBookOpen() to _Excel_BookOpen(), I was surprised to find the read-only functionality missing. It must be an early Alpha version of the rewrite that's bundled in Beta 17? I assume that soon a Beta will contain a newer version of the rewrite? I could cheat and go google up an answer to your question. I do know that 3 years ago, when I first modded a personal version of the function to report locked files, that I also did some cleanup and had decided there was something about hidden worksheets that justified leaving that code intact. I just can't recall what that was Edited August 23, 2013 by Spiff59 Link to comment Share on other sites More sharing options...
water Posted August 23, 2013 Author Share Posted August 23, 2013 Only the rewrite of the Word UDF made it into the latest beta version. The rewrite of the Excel UDF is still on its way and NOT a part of any beta. I don't know why you have been forced to rename your Excel function. I removed the code to search for the first visible worksheet. Can be re-added if someone has a problem based on this decision. 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 September 1, 2013 Author Share Posted September 1, 2013 I'm thinking about adding functions to AutoFilter cells. There will be functions to add/remove filters get a list of set filters What do you expect from a filter function? 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 September 5, 2013 Author Share Posted September 5, 2013 Anyone? 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...
scbuckeye Posted September 6, 2013 Share Posted September 6, 2013 Anyone? I am new to this Forum and new to AutoIt, but I have ran into an AutoFilter issue in other scripts. I would like for AutoFilter to allow for number filters, i.e., less than, greater than, etc. Link to comment Share on other sites More sharing options...
scbuckeye Posted September 6, 2013 Share Posted September 6, 2013 I am trying to wrtie a script to parse a fixed delimited file using the _Excel_BookOpenText. Once the text file is parsed into Excel, I want to copy a range of cells to a new workbook. I have tried using the _Excel_RangeCopyPaste after both workbooks are open. When I do try it this way, AutoIt does not recognize the workbook with the fixed delimited file as an object. After some tweaking I found that I can successfully copy the range of cells once the text file has been saved as a Excel workbook. The problem now is that I cannot get the text file to save as an Excel workbook using the _Excel_BookSaveAs function. Any help would be great. Here is the script that I had tried out working from the examples in the Excel Rewrite.au3 file. This is the script the has the error that the variable is not an object. I do have the full version of SciTE editor installed (ver. 3.3.0) and the latest beta version of AutoIt (ver. 3.3.9.19) Brand new to this forum. I apologize if I am not posting this message in the correct place. expandcollapse popup#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y #AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #include <Excel Rewrite.au3> #include <Constants.au3> ; Create application object and open an example workbook Global $oAppl = _Excel_Open() If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; Open Workbook 2 Global $oWorkbook2 = _Excel_BookNew($oAppl) If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf ; Open Workbook 1 Global $oWorkbook1 = _Excel_BookOpenText($oAppl, @UserProfileDir & "\_Excel2.txt") If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error opening workbook '_Excel2.txt'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf Example1($oWorkbook1, $oWorkbook2) Exit ; ***************************************************************************** ; ; Copy a range of cells from fixed delimited file and paste in new workbook ; ***************************************************************************** Func Example1($oWorkbook1, $oWorkbook2) Local $oRange = $oWorkbook1.Worksheets(1).Range("A1:A3") _Excel_RangeCopyPaste($oWorkbook2.Worksheets(1), $oRange, "A1") If @error <> 0 Then Return MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Range from workbook _Excel2.txt successfully copied to A1.") EndFunc Link to comment Share on other sites More sharing options...
water Posted September 6, 2013 Author Share Posted September 6, 2013 I am new to this Forum and new to AutoIt, but I have ran into an AutoFilter issue in other scripts. I would like for AutoFilter to allow for number filters, i.e., less than, greater than, etc. Thanks for your reply. I'm going to post a filter function quite soon. 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 September 6, 2013 Author Share Posted September 6, 2013 I am trying to wrtie a script to parse a fixed delimited file using the _Excel_BookOpenText. Once the text file is parsed into Excel, I want to copy a range of cells to a new workbook. I have tried using the _Excel_RangeCopyPaste after both workbooks are open. When I do try it this way, AutoIt does not recognize the workbook with the fixed delimited file as an object. After some tweaking I found that I can successfully copy the range of cells once the text file has been saved as a Excel workbook. The problem now is that I cannot get the text file to save as an Excel workbook using the _Excel_BookSaveAs function. Any help would be great. Here is the script that I had tried out working from the examples in the Excel Rewrite.au3 file. This is the script the has the error that the variable is not an object. Need to do some testing and hope to return with a solution quite soon. 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...
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