Jump to content
Sign in to follow this  
tnek

Excel properties for new UDF

Recommended Posts

tnek

I need a list of properties for the excel udf.  For example in post #4 of > water mentions '"Item" is the default property of the Sheets collection.'  So where do I find a list of these collections and properties? 

Share this post


Link to post
Share on other sites
water

MSDN is the place to start.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
tnek

Meh. 

Thanks for the replies, but Autoit is becoming too much like VB for my taste.  I use it because it is simple enough for my DBA and Network Admins to open and modify on the fly like a batch script, but with more power. 

Share this post


Link to post
Share on other sites
JLogan3o13

I'm just curious what you were expecting from a UDF that controls the Excel object but that you would be pointed to MSDN? I don't get the "too much like VB for my taste" comment; AutoIt (nor any other language for that matter) doesn't work by magic, at some point if you want to have any level of skill you're going to have to put some effort in.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
tnek

I am not trying to start anything.

The prior excel udf was simple enough my non-programmers could manipulate it if they needed.  I am not going to ask them to become proficient in Office objects just so they may manipulate their log files.  I am simply going roll back to a prior version of Autoit for them. 

I picked Autoit for them several years ago as it gave them more power and flexibility than regular batch scripting.  The push over the last year or two to make Autoit the equivalent of a full-fledged programming language has moved it beyond their scope.

Share this post


Link to post
Share on other sites
water

The nex Excel UDF is a complete rewrite to support the new Excel file formats (xlsx etc.) and new functions. The rewritten Excel UDF has removed some design weaknesses of the "old" UDF (could only wokr with the active worksheet and workbook etc.).

As the UDF is new I'm sure there is plenty of room for improvement.

What do you think is too complex, missing or doesn't work as expected?

Did you read the script breaking changes section?

Did you have a look at the wiki where I start to explain the new UDF in greater detail?

If you can give me more information I'll be happy to have a look what can be made better.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
DerPensionist

Where are any information/documentation  about handling removed "Visible"  & "Alert" options of Book- ans Sheet-functions?

Share this post


Link to post
Share on other sites
water

In the Wiki - but it is till work in progress.

If you have a specific question I will be happy to assist.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
DerPensionist

Ok,

"Visible" is at _Excel_Open() , but specialy at _Excel_BookSaveAs() seems to be handy "DisplayAlerts" to force write even  book exists. 

Share this post


Link to post
Share on other sites
water

Use "$bOverWrite = True" in this case.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  

  • Similar Content

    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
    • Moonscarlet
      By Moonscarlet
      Hello,
      I am trying to keep only the filtered rows in an excel sheet but couldn't find a fast way to do it for a 1k+ rows file.
      Tried going through each row to see if it's hidden or not and if it is, delete it but it's not fast:
      Local $oExcel = _Excel_Open() Local $File = _Excel_BookOpen($oExcel, $Filepath) Local $LastRow = $File.Activesheet.Range("A1000000").End(-4162).Row _Excel_FilterSet($File, Default, Default, 1, "Test") For $j=1 To $LastRow If $File.Activesheet.Range("A"&$j).EntireRow.Hidden Then _Excel_RangeDelete($File.Activesheet,$j&":"&$j) Next I found a VBA macro that works great but I am having a hard time converting it to be able to use it in my script:
      Sub RemoveHiddenRows() Dim oRow As Range, rng As Range Dim myRows As Range Set myRows = Intersect(Sheets("Sheet1").Range("A:A").EntireRow, Sheets("Sheet1").UsedRange) For Each oRow In myRows.Columns(1).Cells If oRow.EntireRow.Hidden Then If rng Is Nothing Then Set rng = oRow Else Set rng = Union(rng, oRow) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Delete End Sub I would really appreciate it if anyone can help me so I can use this part in my autoit script.
      Thanks.
    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
×