Jump to content

Intro story and asking for an Excel UDF example


Recommended Posts

Yesterday I had to make some little changes to an old Autoit program we use at my work.
The program reads some data and convert it to Excel.
Before writing the cell, it is changed to text and later on I slap the column with an autofitwidth.
Furthermore weI execute a conditional format on the sheet, to make the data more readable.

I quickly found out that because of the breaking changes Excel.udf had starting from AutoIt 3.3.12.0,
a lot of things had to been changed.
The changes I had to do, only took 10 minutes.
After trying to adjust the script for over 5 hours, to get it working with the new Excel.udf, I gave up.

I stopped changing the script, uninstalled the my Autoit and went looking for an older version.
Luckilly I was able to find Autoit v3.3.8.1 (with corresponding Scite) in my software repository.

Installing Autoit V3.3.8.1 and compiling the file, now took me  10 minutes .

 

So why did I not get the old script working with the new Excel.udf?

There are several reasons I failed getting the old script working with the new Excel UDF.

  1.  I had some pressure from management to fix it ASAP (and got a little anxious) :sweating:
  2. Most all resources on the internet point to the working of the old EXcel.UDF
  3. And offcourse there were thosing "breaking changes",
    with new functions using diffrent parameters or using parameters in different order.

One of the column's on the sheet is used to store EAN13 (barcode) and was formatted like 1,23E12.
I couldn't change the cell to text, also autofitwidt was not working and using conditional formatting was also a no-no.
So in the end I could use the new Excel UDF, but not desapointed management.

 

What would I like to ask?
I understand that sometimes you want to rewrite a program to make it better. I even understand that one has to make breaking changes sometimes.
But in this case because of lacking examples/resources my day went completely down the drain.

I would like to ask the Excel.udf developpers to:

  1. Make more functions available to do things like changing cell properties easily, changing cell color, do an autofit columnwidth, format data conditionally. 
     
  2. Or write an Example using the (new) Excel UDF, making examples how to format a cell, do conditional format, changing cell colors etc.
     

I probably am more of an example guy.
Having a good Excel.UDF Example showing a lot of common things normally makes, programming things easy for me.
Because I can keep tweaking snippets until I get it working the way I want it.:)

 

So dear developpers, could you help me and other future user out?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Link to post
Share on other sites

Did you have a look at the wiki? For some/most of your questions you should find an answer there.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

At the time when the Excel UDF was rewritten some of the 3.3.8.1 functions were simply a wrapper to a single command. It was requested to drop those functions as there was a big overhead and on the other hand they didn't cover all possible formatting .

So this information was moved to the wiki where it can easily be extended ;)

$oWorkbook.ActiveSheet.Range("A:C").NumberFormat = "@" ; Formats columns A to C of the active sheet as string
$oWorkbook.ActiveSheet.Range("A:C").Columns.AutoFit ; Set the width of columns A to C to AutoFit

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

@Jemboy  In relation to using old versions of AutoIt.  You can run multiple versions of AutoIt without uninstalling the latest version, or installing older versions.  I have old versions for such this type of situation you had.  You can have the latest version installed, and get the older version zip files from the archive.  You can then extract them to a directory you want to use them.  I prefer the AutoIt installation directory with the version number for folder name from each.  You can then use the #AutoIt3Wrapper_Autoit3Dir to tell SciTE which version of AutoIt to use in what file directory.  Examples below.  

;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.8.1  ;Optionally override the AutoIt3 install directory to use.
;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.12.0  ;Optionally override the AutoIt3 install directory to use.
;~ #AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.14.2  ;Optionally override the AutoIt3 install directory to use.
#AutoIt3Wrapper_Autoit3Dir=C:\Program Files (x86)\AutoIt3\3.3.14.5  ;Optionally override the AutoIt3 install directory to use.

 

Adam

 

Link to post
Share on other sites

@Water: Yes, I did get a look at the wiki (that's how I found out the breaking changes ;)), but for me the wiki is a little to theorotic. I like the have hands on examples I can tweak.
And I must admit, the previous Excel.udf was somewhat sluggischo:) but it worked as a charm.

For now the fire has been "extinguished", but I will look at your commands for autofit and format the cell to text, in the near future.
Would you consider adding an working example script to the wiki showing how the new UDF works with normal things like making text bold, changing color etc. (all the things explained in the wiki.

As stated earlier almost all forum questions and internet postings about the Excel.udf are old postings. So most asked questions have solutions using the old UDF.
It's a matter of time, but time is of the essence:)

Cheers.

 

Link to post
Share on other sites

The wiki has code snippets for every explained formatting option. Adding full working examples would only repeat code you should already know.

Example:
Now you find

$oRange.Font.Size = 12

A full working script would look like:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkBook = _Excel_BookNew($oExcel)
Global $oRange = $oWorkbook.Activesheet.Range("A1")
$oRange.Font.Size = 12

Each formatting option would add this overhead.

So for the time being I don't think it is sensible to add full working examples :)

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

@water: I still believe a big Excel.udf example would bring down the learning curve to learn how to use the UDF.
I myself learn the most by tweaking the diffirent parameters and adding "Msgboxes" to check variable values.
Also objects are not my forté, for I am from the pre object area :)

But thanks for posting a basic Excel framework I can use in the future.

 

Link to post
Share on other sites

We have a lot of sources where you can get information from:

  • Every function of the Excel UDF comes with at least one example
  • A lot of functions that the Excel UDF does not cover are explained in the wiki - including code snippets to include in your script.
    As the Excel UDF is just a wrapper for Microsofts Excel COM you need some understanding how this works to use the code snippets.
  • For everything else you need to get your feet wet with the MS documentation of the Excel object model (example for Excel 2013)

If there is some urgent need for functions which the UDF and the wiki do not cover at the moment I might add them to the wiki.
Please post now ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

@water: For new fuctions, I would suggest (universal) functions:

  • to format cells (color, hight, font, background etc.),
  • turn on/off filter,
  • filter certain column conditionally (e.g. color, containing..., sort A-Z, Z-A_
  • conditional formatting
  • freeze row / column
  • importing data e.g. CSV

Basically what most people do manually.

As an example, every month we download  a CSV from our VOIP-provider. It contains every call made during the month.
However the file also contains the calls from multiple locations.
In the future, I would like to make a script to do:

 select csv, import into Excel, make phone numbers text, turn on filter, freeze first row, show only calls from 1 number/location,
format the sheet conditionally (so not to strain the eyes;)), do this for all locations, and mail the files to the location managers.

 

 

 

Link to post
Share on other sites
  • to format cells (color, hight, font, background etc.) => can be found in the wiki
  • turn on/off filter => see example script for _Excel_FilterSet
  • filter certain column conditionally (e.g. color, containing..., sort A-Z, Z-A) => Need to do some investigation if this can be solved using _Excel_RangeFilterSet
  • conditional formatting => can be quite complex. As you are the first asking for this functionality I'm not quite  sure it is worth the effort to modify the UDF.
  • freeze row / column => can be found in the wiki
  • importing data e.g. CSV => See _Excel_BookOpen or for advanced import _Excel_BookOpenText

If you need a special function not covered by the Excel UDF you can always start the macro recorder, do what you want to automate, stop the recorder and then translate the resulting VBA code to AutoIt.
Or create a template workbook by hand and just fill in the data using AutoIt.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
    • By jmp
      Hello.
      I have IETable,
      Get by this code
      $oTable = _IETableGetCollection ($oIE, 1) $aTableData = _IETableWriteToArray ($oTable) Local Const $iArrayNumberOfCols = UBound($aTableData, $UBOUND_COLUMNS) Local Const $iArrayNumberOfRows = UBound($aTableData, $UBOUND_ROWS) Local $aArraySubstringsRow[$iArrayNumberOfCols] ;~ Local $aExtract = _ArrayExtract ($aTableData, 1, 1, 1, -1) ;~ MsgBox(0, "", $iArrayNumberOfCols) ;~ _ArrayDisplay($aExtract) Local Const $iArrayRowIndex = 1 Local $sSubstring For $i = 0 To $iArrayNumberOfCols - 1     $sSubstring = StringLeft($aTableData[$iArrayRowIndex][$i], 2)     $aArraySubstringsRow[$i] = $sSubstring Next _ArrayDisplay($aArraySubstringsRow, "This is a row") and i want to use cell (52, 82, 18, 9,...10) one by one for selecting dropdown box in internet explorer.

      So, How to show/get/extract cell one by one (in msgbox)? 
×
×
  • Create New...