Yet Another -- ExcelCOM UDF

[big_daddy]

The first post has been fixed!

[enaiman]

Thank you very much

[koiron]

I'm so glad this is being included as part of autoit. Thanks for your help guys You have no idea how much time this saves me and projects I use this in at work.

Edited July 30, 2008 by koiron

[Spiff59]

Just a "tweaking" comment...

Wouldn't it be better to avoid a guaranteed 8 comparisons performed by the following code from the often-used _ExcelBookOpen:

If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)

And limit it to 2 comparisons per call?

If $sPassword > "" Then
            If $sWritePassword > "" Then 
                .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
            Else
                .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
            EndIf
        Else
            If $sWritePassword > "" Then 
                .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
            Else
                .WorkBooks.Open($sFilePath, Default, $fReadOnly)
            EndIf
        EndIf

The same password code above exists again in _ExcelBookSaveAs, just under the:

If $sType = "xls" Or $sType = "csv" Or $sType = "txt" Or $sType = "template" Or $sType = "html" Then
        If $sType = "xls" Then $sType = $xlNormal
        If $sType = "csv" Then $sType = $xlCSVMSDOS
        If $sType = "txt" Then $sType = $xlTextWindows
        If $sType = "template" Then $sType = $xlTemplate
        If $sType = "html" Then $sType = $xlHtml
    Else
        Return SetError(2, 0, 0)
    EndIf

Wouldn't that execute faster in a Switch/Case statement or as nested-IF's?

[squishy]

Hello!

First off, this is a great tool for those of us who use Excel sheets on a regular basis! Thank you!

I have a suggestion...what about adding in a sheet variable in all the functions. This would allow us to specify which sheet to write the data, instead of activating it first. Something like the following:

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1,$vSheet = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT IsNumber($vSheet) Or $vSheet < 0 Or $vSheet > $oExcel.Sheets.Count Then Return SetError(3, 0, 0)
    If $vSheet = 0 Then $vSheet = $oExcel.Activesheet.Index
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        $oExcel.Sheets($vSheet).Cells($sRangeOrRow, $iColumn).Value = $sValue
        Return 1
    Else
        $oExcel.Sheets($vSheet).Range($sRangeOrRow).Value = $sValue
        Return 1
    EndIf
EndFunc;==>_ExcelWriteCell

If you add the $vSheet option at the end of the statement, it won't break any current code. Just a thought for future releases.

I am also looking at trying to optimize the array reading/writing, instead of doing it one cell at a time. It can be perfomed in VBA easily, so there has to be a way using AutoIt...

Thanks again!

[dmob]

Let me know if there are functions that you would like to see included in the new version and I will add it to the top of the list.

Thanks

A function to protect the sheet or range of cells. _ExcelRangeLock or _ExcelRangeProtect or similar would be a blessing

[ToyBoi]

this is one of my favorite UDFs! just a quick suggestion, do we have a function for sheet.copy yet? (copy sheet to another workbook) and macro importing

[rec9140]

_ExcelSheetUsedRangeGet()

I am using an older version as I need to use _ExcelSheetUsedRangeGet(), definately a must have to add to the newer version.

Something I could use to replace clunky keystroke feeds:

_ExcelSheetPasteClipboard

ActiveSheet.Paste Destination:=Worksheets("Sheet1")

Not an array, not a string, just paste what ever is on the clipboard to the sheet.

I've got what I need on the clipboard, I just need it pasted there and sending keystrokes seems to be quite tempermental.

Thanks for a great UDF, really helps a lot with my currrent script.

[DarrenBaker]

Hi guys,

I have two suggestions for the _ExcelBookAttach function:

• That the function allow searching of substrings in Name, FullName, and Title
• That the function cycle through not just workbooks, but Excel applications as well, since if you have more than one instance of Excel open, the function will only return workbooks from the last-used instance of Excel.exe.

Thanks!

Darren

[Mercury049]

The working list of functions on the first page of this thread show _ExcelSheetUsedRangeGet() But when I try to use it, it fails. I looked in Excel.au3 and did not find it listed in there? Why is it on the working functions list?

Anyone have a way get the used range of a sheet until this function is added/fixed?

Thanks

[dj200]

Hi Kerros,

if possible, could you please create a new TextToColumns function for me? Basically I want to convert one column from Number to Text. The easy way to achieve my goal is to use TextToColumns function.

I could easily do that in Excel: the VBA code liks this:

Columns("F:F").Select

Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 2), TrailingMinusNumbers:=True

But I spent around 4 hours to change this VBA to Autoit3, but never succeded. Below is my code.

$oExcel.columns("F:F").select

Dim $aArray[2]=(1,2)

$oExcel.Selection.TextToColumns($oExcel.range("F1"),1,1,true,true,true,false,false,false,$aArray,1)

Could you and anyone help? thanks.

Here is a function that I put together to do the TextToColumns function, based largly or almost totally on LocoDarwin's code.

;===============================================================================
;
; Description:      Parses a column of cells that contain text into several columns.
; Syntax:           _ExcelTextToColumns($oExcel,',', 'A1:A17')
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $DelimChar - Charater that the text is delimitated by
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the copy procedure (left)
;                   $iRowEnd - The ending row for the copy procedure (bottom)
;                   $iColEnd - The ending column for the copy procedure (right)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
; Author(s):        SEO <locodarwin at yahoo dot com>base code  <Kerros at gamil dot com>
; Example:          _ExcelTextToColumns($oExcel,',','A1:A17')
; Note(s):          None
;
;===============================================================================

Func _ExcelTextToColumns($oExcel,$DelimChar ,$sRangeOrRowStart , $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
    Else
        $oExcel.Range($sRangeOrRowStart).Select
    EndIf

$oExcel.Selection.TextToColumns(Default,Default,Default,Default,False,False,False,False,True,$DelimChar ,Default,Default,Default,Default)
    Return 1
EndFunc

The function probally could use some more error checking and such, but it's working for me ATM.

I know it's only one line of original code, but it's a function that i use consistantly.

If you feel that this is a valid addition, feel free to use in the next release of the UDF.

Kerros

[Kerros]

I think that this is what you are trying to do:

Local $array[2]=[1,2]
_ArrayDisplay($array)
$oExcel.columns("F:F").select
$oExcel.Selection.TextToColumns(Default, 'xlDelimited', 'xlDoubleQuote',False,True,False,False,False,False,$array,Default,Default,True)

let me know if it works for you

Kerros

[dj200]

Thank you for the help. Unfortunately, it does not work. Basically I want to user TEXTTOCOLUMNS function to convert some numbers to Text fields in Excel: For example,

Number TEXT

123 '123

456 '456

786 '789

Any thoughts? Thanks.

I think that this is what you are trying to do:

Local $array[2]=[1,2]
_ArrayDisplay($array)
$oExcel.columns("F:F").select
$oExcel.Selection.TextToColumns(Default, 'xlDelimited', 'xlDoubleQuote',False,True,False,False,False,False,$array,Default,Default,True)

let me know if it works for you

Kerros

[MatteoGuallini]

If I read a cell I get the value of this cell. How can I get the formula of this cell instead of the value?

[MatteoGuallini]

This modified function get formulas

;===============================================================================
;
; Description:    Read information from the active worksheet of the specified Excel object.
; Syntax:          $val = _ExcelReadCellFormula($oExcel, $sRangeOrRow, $iColumn = 1)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sRangeOrRow - Either an A1 range, or an integer row number to read from if using R1C1
;                   $iColumn - The column to read from if using R1C1 (default = 1)
; Requirement(s):   None
; Return Value(s):  On Success - Returns the data from the specified cell
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Specified parameter is incorrect
;                           @extended=0 - Row out of valid range
;                           @extended=1 - Column out of valid range
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        This function will only read one cell per call - if the specified range spans
;                   multiple cells, only the content of the top left cell will be returned.
;
;===============================================================================

Func _ExcelReadCellFormula($oExcel, $sRangeOrRow, $iColumn = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Formula
    EndIf
EndFunc;==>_ExcelReadCell

[xtremaxs]

Has anyone figured out how to perform TextToColumns delimted on a comma?

[cageman]

im trying to figure out how to change the query link in excel. Whats working now is a macro connected with a button. If i click it it automaticly reloads the query with the query link i put in some cell. Problem is now. How can i make autoit hit that button ?

preferably without mouseclicks , so just excel functions

Edited January 30, 2009 by cageman

[Wooltown]

The easiest way to do it is:

_ExcelNumberFormat($l_ExcelReport,"@","A1:A65536")

@ means it convert it to text

[Wooltown]

For Full column:

$oExcel.ActiveSheet.Range("A1").EntireColumn.NumberFormat = "@"

For Full row:

$oExcel.ActiveSheet.Range("A1").EntireRow.NumberFormat = "@"

[Wooltown]

Update to _ExcelCreateBorders

Replace the Excel constants in the UDF as specified in this link:

http://www.autoitscript.com/forum/index.ph...=xlSlantDashDot

This is the alternatives for border:

$xlContinuous

$xlDash

$xlDashDot

$xlDashDotDot

$xlDot

$xlDouble

$xlSlantDashDot

$xlLineStyleNone - To remove the Border

Found the example here:

http://www.java2s.com/Code/VBA-Excel-Acces...lContinuous.htm

Edited February 6, 2009 by Wooltown