Sign in to follow this  
Followers 0
GioVit

New ExcelUDF

17 posts in this topic

#1 ·  Posted (edited)

This is a new approach to Excel interface via COM.

it is fully expandable, but don't know what else to add.

suggestion are accepted

*** Before Running ****

- Download all three files and save it in the same folder.

- Rename WorldCup.au3 to WorldCup.xls

- Run the test progam "ExcelTestGUI2.au3"

then take a look to ExcelUDF to see how easy is to expand it.

needs Beta Version.

not fully tested yet.

06/18/2006 New version 01 with some bugs fixed and added $XL_GETALLNAMES see _XL_GETALLNAMESExample.au3

Enjoy it.

ExcelTestGUI2.au3

WorldCup.au3

_XL_GETALLNAMESExample.au3

ExcelUDF_v01.au3

Edited by GioVit

Share this post


Link to post
Share on other sites



It works well! Although I don't need to use this kind of thing, I'm sure someone will use it.


My Programs:AInstall - Create a standalone installer for your programUnit Converter - Converts Length, Area, Volume, Weight, Temperature and Pressure to different unitsBinary Clock - Hours, minutes and seconds have 10 columns each to display timeAutoIt Editor - Code Editor with Syntax Highlighting.Laserix Editor & Player - Create, Edit and Play Laserix LevelsLyric Syncer - Create and use Synchronised Lyrics.Connect 4 - 2 Player Connect 4 Game (Local or Online!, Formatted Chat!!)MD5, SHA-1, SHA-256, Tiger and Whirlpool Hash Finder - Dictionary and Brute Force FindCool Text Client - Create Rendered ImageMy UDF's:GUI Enhance - Enhance your GUIs visually.IDEA File Encryption - Encrypt and decrypt files easily! File Rename - Rename files easilyRC4 Text Encryption - Encrypt text using the RC4 AlgorithmPrime Number - Check if a number is primeString Remove - remove lots of strings at onceProgress Bar - made easySound UDF - Play, Pause, Resume, Seek and Stop.

Share this post


Link to post
Share on other sites

It works well! Although I don't need to use this kind of thing, I'm sure someone will use it.

This UFD is aimed for thouse who know more Excel than VBA.

but thanks for try it.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Hi,

This looks great!

I will check it out.. i have been wanting someone to create a well-written version, compared to mine.

I will take some time to convert old macros, though!

1. Are you able to show me the syntax to convert all my example macros to this?

I will try to start making up a document.

2. I see that you are using the constants to define the function; will this not make it hard for a syntax checker like Scite to help with this eventually?

3. Your function names do not intuitively help out which one to use, either; I'll have to give it some time

4. Using one of your functions, which do not specify the filename, how do you know which excel sheet it might read from if you have a number open at once? (eg "$Result = _XLRange($XL_READVALUE, "D1:D3",1,2)"). Do you have to keep checking you have it open and activated each time? [seems like a lot of repetitive lines and bother...?]

5. I do get an error with one of the functions "_XLRange($XL_AUTOFILTER,"A1")"

Best, randall

Edited by randallc

Share this post


Link to post
Share on other sites

Hi randalllc, nice to chat with you, sorry for the late answer I was off line.

about your questions

1. Are you able to show me the syntax to convert all my example macros to this?

Please show me some of the example macros you want me to convert

2. I see that you are using the constants to define the function; will this not make it hard for a syntax checker like Scite to help with this eventually?

yes you are right but I think that if I use string constants user can make more mistakes

3. Your function names do not intuitively help out which one to use, either; I'll have to give it some time

I think that is well structured, Function name says which object you want to use and the $Op parameter indicates which operation do you wish to apply to that object, note also that there are operations that can be applied to diffrent objects ($XL_NEW, $XL_DELETE).

4. Using one of your functions, which do not specify the filename, how do you know which excel sheet it might read from if you have a number open at once? (eg "$Result = _XLRange($XL_READVALUE, "D1:D3",1,2)"). Do you have to keep checking you have it open and activated each time? [seems like a lot of repetitive lines and bother...?]

when you don't specify a workbook it uses ActiveWorkbook and the same is applied for worksheet (Activesheet) there are three internal function called GetValidWB, GetValidWS and GetValidRange that allways try to return a valid Object Workbook, Worksheet or Range respectively or it will return an error

5. I do get an error with one of the functions "_XLRange($XL_AUTOFILTER,"A1")"

is this a COM or AutoIt error ? if is a COM what is the error number?

Regards

GioVit

Share this post


Link to post
Share on other sites

PS

Getting there slowly; how would you detect if an excel file is already open, and , if so, is there an error message?; or how would you activate it without getting an "already open" message?

Best, Randall

My UDF don't detect if an excel file is already open, but it don't allow you to open a workbook via a FileOpen dialogbox, so if you make a script you will have the control of which workbook open and how many times to open too.

Regards GioVit

PS Sorry about my Spanglish

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Hi,

Your script never activates the worldcup sheet, then gives;

We intercepted a COM Error !

err.description is: Unable to get the AutoFilter property of the Range class

err.number is: 80020009

err.scriptline is: 497

you will have the control of which workbook open and how many times to open too.

Sure, but i'd like to be able to do that via the script..?

Randall

*** PS All my example scripts are on the first post in the thread link, first in my signature...***** "ExcelCOM UDF"

Edited by randallc

Share this post


Link to post
Share on other sites

Hi,

Your script never activates the worldcup sheet, then gives;

Sure, but i'd like to be able to do that via the script..?

Randall

*** PS All my example scripts are on the first post in the thread link, first in my signature...***** "ExcelCOM UDF"

Did you renamed the file "WorldCup.au3" to "WorldCup.xls"

Share this post


Link to post
Share on other sites

*** PS All my example scripts are on the first post in the thread link, first in my signature...***** "ExcelCOM UDF"

Ok I will try to make the conversion and will add a function to change the properties in a Range like (Font Name, Font Size, etc)

Share this post


Link to post
Share on other sites

thanks;

(Renaming; missed it!)Sorry about that; could you not put it in as "xls"?

Best, Randall

Nop, forum don't let me upload it

Share this post


Link to post
Share on other sites

Can I get names of all open workbooks (ie not sheets) easily with your func? - I think mine failed mostly!

Randall

will add in next version with $XL_GETALLNAMES

here is the first file converted

;_XLArrayByLastRowEx2.au3
#include"ExcelUDF v01.au3"
#include<Array.au3> 
_XLApp($XL_OPEN)
$s_FilePath=@ScriptDir&"\WorldCup.xls"
_XLWBook($XL_OPEN, $s_FilePath)
$LR = _XLCell($XL_LASTROW, "H1")
$XLArray =_XLRange($XL_READVALUE, "H1:"& _XLRange($XL_GETADDRESS,$LR))
;==============================================================================
local $s_StringOfSingleLine
for $i=0 to ubound ($XLArray,2)-1
$ar_ArrayOfSingleLine=_Array2DTo1D( $XLArray, "Array contents", 0, $i,0); $s_i_Column = 0)
_ArrayDelete($ar_ArrayOfSingleLine,0)
$s_StringOfSingleLine&=_ArrayToString($ar_ArrayOfSingleLine,",")&@CRLF
Next
$s_ArrayPath=@ScriptDir&"\array.csv"
FileDelete($s_ArrayPath)
;MsgBox(0,"","$s_ArrayFileString="&@CRLF&$s_StringOfSingleLine)
FileWrite($s_ArrayPath,$s_StringOfSingleLine)
RunWait("Notepad.exe " & $s_ArrayPath)
_XLApp($XL_CLOSE)

Func _Array2DTo1D(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1, $Line = 0, $s_i_Column = 0)
    ; Change Line "X" to 1 dimensional array; [Randallc - I have ***lifted it from Forum at some stage]
    Local $output = ""
    Local $r, $e, $Swap
    If $n_Index <> 0 Then $n_Index = 1; otherwise I can't cope!
    If $s_i_Column <> 0 Then $s_i_Column = 1; otherwise I can't cope!
    If $Line < 0 Then $Line = 0; otherwise I can't cope!
    ;If $Line>UBound($ar_Array,1+($s_i_Column=0))+($n_Index=0)-2 then $Line=UBound($ar_Array,)+($n_Index=0)-2   ; otherwise I can't cope!
    If $Line > UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2 Then $Line = UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2; otherwise I can't cope!
    Dim $Array[UBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0) ]
    $Array[0] = UBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0)
    If Not IsArray($ar_Array) Then Return -1
    For $r = $n_Index To UBound($ar_Array, 1 + $s_i_Column) - 1
        $e = $r
        $NewLine = $Line
        If $s_i_Column = 1 Then
            $NewLine = $r
            $e = $Line
        EndIf
        $Array[$r+ ($n_Index = 0) ] = $ar_Array[$e][$NewLine]
    Next
    ;_ArrayDisplay($Array,$s_Title&"Line"&$Line)
    Return $Array
EndFunc   ;==>_Array2DTo1D

Regards

Share this post


Link to post
Share on other sites

Hi,

1. Are you able to show me the syntax to convert all my example macros to this?

Best, randall

another conversion

;_XLArrayExample2Single.au3
#include"ExcelUDF v01.au3"
#cs
;#include <Array.au3>
$FilePath=@ScriptDir&"\Blank5.xls"
$DataString="12,7,6,9,23,45,3,17,18,9"&@CRLF&"3,12,7,6,9,23,45,3,17,18"&@CRLF&"3,12,8,6,9,23,45,3,17,18"&@CRLF&"3,12,9,6,9,23,45,3,17,18"
$DataString=StringReplace($DataString,",",@TAB)
;MsgBox(0,"","$DataString="&$DataString)
$XLRange=_XLpaste($FilePath,1,"Z",11,$DataString,1)
;MsgBox(0,"","$XLRange="&$XLRange)
$XLArray=_XLArrayRead($FilePath,1,$XLRange)
_Array2dDisplay($XLArray, "Array as read",0)
_Array2DTo1D($XLArray,"Array", 0,9,1)
_XLShow($FilePath,1,"Z",11)
MsgBox(0,"","Here, Sheet 1")
_XLClose($FilePath,1)
#ce

_XLApp($XL_OPEN,False)
_XLWBook($XL_NEW)
Dim $DataString [4][10] = [[12,7,6,9,23,45,3,17,18,9],[3,12,7,6,9,23,45,3,17,18],[3,12,8,6,9,23,45,3,17,18],[3,12,9,6,9,23,45,3,17,18]]
$XLRange =_XLRange($XL_WRITEVALUE,"Z11","","", $DataString)
$XLArray = _XLRange($XL_READVALUE,$XLRange)
_Array2dDisplay($XLArray, "Array as read",0)
_Array2DTo1D($XLArray,"Array", 0,9,1)
_XLApp($XL_VISIBLE)
_XLRange($XL_SELECT,$XLRange)
MsgBox(0,"","Here, Sheet 1")
_XLWBook($XL_CLOSE)
_XLApp($XL_CLOSE)

Func _Array2DTo1D(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1, $Line = 0, $s_i_Column = 0)
    ; Change Line "X" to 1 dimensional array; [Randallc - I have ***lifted it from Forum at some stage]
    Local $output = ""
    Local $r, $e, $Swap
    If $n_Index <> 0 Then $n_Index = 1; otherwise I can't cope!
    If $s_i_Column <> 0 Then $s_i_Column = 1; otherwise I can't cope!
    If $Line < 0 Then $Line = 0; otherwise I can't cope!
    ;If $Line>UBound($ar_Array,1+($s_i_Column=0))+($n_Index=0)-2 then $Line=UBound($ar_Array,)+($n_Index=0)-2   ; otherwise I can't cope!
    If $Line > UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2 Then $Line = UBound($ar_Array, 1+ ($s_i_Column = 0)) + ($n_Index = 0) - 2; otherwise I can't cope!
    Dim $Array[UBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0) ]
    $Array[0] = UBound($ar_Array, 1 + $s_i_Column) + ($n_Index = 0)
    If Not IsArray($ar_Array) Then Return -1
    For $r = $n_Index To UBound($ar_Array, 1 + $s_i_Column) - 1
        $e = $r
        $NewLine = $Line
        If $s_i_Column = 1 Then
            $NewLine = $r
            $e = $Line
        EndIf
        $Array[$r+ ($n_Index = 0) ] = $ar_Array[$e][$NewLine]
    Next
    ;_ArrayDisplay($Array,$s_Title&"Line"&$Line)
    Return $Array
EndFunc   ;==>_Array2DTo1D

Func _Array2dDisplay(ByRef $ar_Array, $s_Title = "Array contents", $n_Index = 1)
    ; Display 2 dimensional array; [***lifted from Forum]
    Local $output = ""
    Local $r, $c
    If Not IsArray($ar_Array) Then Return -1
    For $r = $n_Index To UBound($ar_Array, 1) - 1
        $output = $output & @LF
        For $c = 0 To UBound($ar_Array, 2) - 1
            $output = $output & $ar_Array[$r][$c] & " "
        Next
    Next
    MsgBox(4096, $s_Title, $output)
    Return
EndFunc   ;==>_Array2dDisplay

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

Hi there! I'm really new to AutoIt, I need some help with "New ExcelUDF". It works fine for me but for my script I need the last row of an Excel worksheet as number (not as selected range as the function _XLCell($XL_LASTROW,"A1") returns it). Basically I want to make in my script a for-next loop from first row to last row. When I give the values manually it works fine. Can you give me some help with that?

Keep up the good work

Never mind!! I followed your function's code and I got what i wanted! Thanx anyway!

Edited by sbad

Share this post


Link to post
Share on other sites

Hi there! I'm really new to AutoIt, I need some help with "New ExcelUDF". It works fine for me but for my script I need the last row of an Excel worksheet as number (not as selected range as the function _XLCell($XL_LASTROW,"A1") returns it). Basically I want to make in my script a for-next loop from first row to last row. When I give the values manually it works fine. Can you give me some help with that?

Keep up the good work

Never mind!! I followed your function's code and I got what i wanted! Thanx anyway!

i

Hi, sbad sorry for late answer but I'm vey busy now, next week I will try to update ExcelUDF for an easy use and post little tutorial.

About your question to get a row or a column from a range (Cell in this case) use:

GetRowColAddr(_XLCell($XL_LASTROW,"A1"), True, False) ; Return the last Row number
GetRowColAddr(_XLCell($XL_LASTCOL,"A1"), False, False) ; Return the last Column letter
GetRowColAddr(_XLRange($XL_SELECT,"A1:C5"), True, True) ; Return the Rows  number in format  "Num:Num" ie "1:5"
GetRowColAddr(_XLRange($XL_SELECT,"A1:C5"), False, True) ; Return the Columns letters if format "Letter:Letter" ie "A:C"

regards

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0