Update the Excel UDF that comes with AutoIt

[water]

Give function _Excel_BookOpenText a try.

[nitekram]

@water

Looking at it now, thanks you.

What about importing an Array?

edit think I found it? _Excel_RangeWrite()

Edited July 14, 2013 by nitekram

[water]

Exactly, _Excel_RangeWrite is the function you need.

[apoliemans]

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

[water]

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.

[apoliemans]

@water,

I see, thanks for explaining the procedure.

[water]

Released Alpha 7. For download and a history of changes please see post #1.

[water]

Released Alpha 8.

For download and a history of changes please see post #1.

[water]

Released Beta 1.

For download and a history of changes please see post #1.

[water]

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!

[Spiff59]

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

[water]

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

[Spiff59]

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

[water]

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.

[water]

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?

[water]

Anyone?

[scbuckeye]

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.

[scbuckeye]

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

[water]

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.

[water]

 

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.