GioVit Posted June 17, 2006 Share Posted June 17, 2006 (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.au3Enjoy it.ExcelTestGUI2.au3WorldCup.au3_XL_GETALLNAMESExample.au3ExcelUDF_v01.au3 Edited June 18, 2006 by GioVit Link to comment Share on other sites More sharing options...
RazerM Posted June 17, 2006 Share Posted June 17, 2006 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. Link to comment Share on other sites More sharing options...
GioVit Posted June 17, 2006 Author Share Posted June 17, 2006 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. Link to comment Share on other sites More sharing options...
randallc Posted June 18, 2006 Share Posted June 18, 2006 (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 June 18, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
randallc Posted June 18, 2006 Share Posted June 18, 2006 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 ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 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 timeI 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 Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 PSGetting 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, RandallMy 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 GioVitPS Sorry about my Spanglish Link to comment Share on other sites More sharing options...
randallc Posted June 18, 2006 Share Posted June 18, 2006 (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 classerr.number is: 80020009err.scriptline is: 497you 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 June 18, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 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" Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 *** 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) Link to comment Share on other sites More sharing options...
randallc Posted June 18, 2006 Share Posted June 18, 2006 thanks; (Renaming; missed it!)Sorry about that; could you not put it in as "xls"? Best, Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
randallc Posted June 18, 2006 Share Posted June 18, 2006 Can I get names of all open workbooks (ie not sheets) easily with your func? - I think mine failed mostly! Randall ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 thanks;(Renaming; missed it!)Sorry about that; could you not put it in as "xls"?Best, RandallNop, forum don't let me upload it Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 Can I get names of all open workbooks (ie not sheets) easily with your func? - I think mine failed mostly! Randallwill add in next version with $XL_GETALLNAMES here is the first file converted expandcollapse popup;_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 Link to comment Share on other sites More sharing options...
GioVit Posted June 18, 2006 Author Share Posted June 18, 2006 Hi, 1. Are you able to show me the syntax to convert all my example macros to this? Best, randall another conversion expandcollapse popup;_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 Link to comment Share on other sites More sharing options...
sbad Posted June 20, 2006 Share Posted June 20, 2006 (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 June 21, 2006 by sbad Link to comment Share on other sites More sharing options...
GioVit Posted June 21, 2006 Author Share Posted June 21, 2006 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 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