Update the Excel UDF that comes with AutoIt

[water]

Released Alpha version 6. For download and a history of changes please see post #1.

[Andreu]

Hey Water,

Trying to run the example for _Excel_RangeRead, and keep getting this:

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\Dreu\Desktop\WaterEXCEL\_Excel_RangeRead.au3"
C:\Program Files (x86)\AutoIt3\Include\Excel Rewrite.au3 (100) : ==> The requested action with this object has failed.:
$oExcel.Calculation = $iCalculation
$oExcel.Calculation = $iCalculation^ ERROR
>Exit code: 1    Time: 0.822

I've tried rewriting the example following the UDF, but still get that message. Line 90 has to do with opening the book... Most of my excel files from work have a xlsm extension due to having most of my scripts heavy laden with VB. Thought that may be the problem (because I originally just dove right in and started using your UDF), so I retracted and just tried to run the example, and after it works slowly modify it as my own to catch what I was doing wrong.

I'm out of ideas at this point, wondering if you could help me out.

Thanks

EDIT:

After playing around, I found something. If I open Excel, any file... (Left at default "Book1") and run it... It runs just fine. 

EDIT2:

Still don't know what causes the error when there's no Excel object currently open, but as long as I open one up before running it... It runs amazing. Very very fast, processing a ton of data. My business support leads reporting to me thank you.

Edited June 26, 2013 by Andreu

[water]

Andreu,

the problem you see is caused by:

1. You running an AutoIt version < 3.3.9.2. COM error handling has been enhanced with the latest beta versions.
   The rewrite of the Excel UDF is written for the latest beta versions and will crash with anys other version.
2. setting the calculation mode in function _Excel_Open. Looks like this is not sensible and only helps when there is an open workbook.
   I will re-think the subject; maybe I'll remove the feature

What you can do is:

• Remove the line from _Excel_Open
• Run the latest AutoIt beta version

Edited June 26, 2013 by water

[Andreu]

That did the trick Water. Been using your UDF and I'm quite thoroughly impressed. Really great work here, and a world of difference from the default UDF. 

I use Excel 9-10 hours a day (more during bills time of the month, as then I use it at home)... 5 days a week, nearly 300 days a year.

I am very very well versed in Excel and using VB with Excel, so I know the difference in what AutoIt can do vs. Excel by itself... Coupling this UDF with the advantages AutoIt provides, is going to make my workload quite a bit easier and for that I thank you.

Here are some thing I've found so far while using your UDF. (2 typo's, 1 (bug? Let you be the judge of that))

UDF Typos

 

The syntax states the function names. (The names are correct, syntax is not.)

 

; Name...........: _Excel_SheetAdd

; Syntax.........: _Excel_SheetAddNew

 

; Name...........: _Excel_ConvertFormula

; Syntax.........: _Excel_Close

 

Bugs?

 

Using a sheet name in SheetAdd causes Extended Error Code 5, but same code... No sheet name = no error. **Adds the sheet, doesn't name it.

My Code: (@Error)

$AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1, "AgentData")

 

My Code: (No Error)

$AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1)

Edited June 26, 2013 by Andreu

[water]

Andreu,

thanks a lot for your reply!

I alread fixed the name issues.

I tested _Excel_SheetAdd with the parameters you described but couldn't recreate the problem.

Some questions:

• $OrgChart_WB is a workbook object?
• How many sheets does the Workbook have before calling _Excel_Sheetadd?

[Andreu]

Most definitely. I'm writing a very extensive program right now to handle quite a bit of the more repetitive reporting tasks at work, and streamline the process for my direct reports a bit more so I'll but using the hell out of your UDF, as I find things I'll let you know.

It starts off with 3 sheets, ["Sheet1", "Sheet2", "Sheet3"]

Here is the definition of my excel handles, and how/where their called.

Local $headers[1][9] = [["#", "#", "#", "#", "#", "#", " #", "#", "#"]]; Headers Array
   $OrgChart_OB = _Excel_Open(Default, True, Default, True); Excel Object
   If @error <> 0 Then Return MsgBox(16, "Excel", "Error creating a new Excel application object.")
   $OrgChart_WB = _Excel_BookNew($OrgChart_OB); Excel Workbook handle
   $AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1); Agent sheet handle
   _Excel_RangeWrite($OrgChart_OB, $OrgChart_WB, $AgentDataSheet, $headers, 1, 1); Add our headers

When you suggested I do so, I updated to the latest beta. (3.3.9.4), and I'm running a fully licensed 2010 Excel out of the full Office pkg.

Need any data/other info at all, just let me know. I'll check back here periodically. Wasn't expecting you to be awake lol.

Edit1: Removed real header names for confidentiality reasons.

Edited June 26, 2013 by Andreu

[Andreu]

Most definitely. I'm writing a very extensive program right now to handle quite a bit of the more repetitive reporting tasks at work, and streamline the process for my direct reports a bit more so I'll but using the hell out of your UDF, as I find things I'll let you know.

It starts off with 3 sheets, ["Sheet1", "Sheet2", "Sheet3"]

Here is the definition of my excel handles, and how/where their called.

Local $headers[1][9] = [["#", "#", "#", "#", "#", "#", " #", "#", "#"]]; Headers Array
   $OrgChart_OB = _Excel_Open(Default, True, Default, True); Excel Object
   If @error <> 0 Then Return MsgBox(16, "Excel", "Error creating a new Excel application object.")
   $OrgChart_WB = _Excel_BookNew($OrgChart_OB); Excel Workbook handle
   $AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1); Agent sheet handle
   _Excel_RangeWrite($OrgChart_OB, $OrgChart_WB, $AgentDataSheet, $headers, 1, 1); Add our headers

When you suggested I do so, I updated to the latest beta. (3.3.9.4), and I'm running a fully licensed 2010 Excel out of the full Office pkg.

Need any data/other info at all, just let me know. I'll check back here periodically. Wasn't expecting you to be awake lol.

Edit1: Removed real header names for confidentiality reasons.

As you can see in the previous code, I assigned the agent sheet a handle ($AgentDataSheet)

Since then, I've made the script create a new tab $OrgChartSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1); Org Chart sheet handle

However, regardless what I do... the below code reads from the currently active tab. (In this case, $OrgChartSheet)

Local $pullAssociates = _Excel_RangeRead($OrgChart_OB, $OrgChart_WB, $AgentDataSheet, "A1:I"&$Rows+1, 1)

Acts as if it ignores the worksheet parameters.

[water]

It's noon here, so I should be awake

Strange problem. I ran the following script and it works just fine:

#include <excel rewrite.au3>
Local $headers[1][9] = [["N", "T", "U", "S", "F", "L", "A", "N", "P#"]]; Headers Array
$OrgChart_OB = _Excel_Open(Default, True, Default, True); Excel Object
If @error <> 0 Then MsgBox(16, "Excel", "Error creating a new Excel application object.")
_Excel_ErrorNotify(2)
MsgBox(0, "", @error & "-" & @extended)
$OrgChart_WB = _Excel_BookNew($OrgChart_OB); Excel Workbook handle
If @error <> 0 Then MsgBox(16, "Excel", "Error creating a new Excel Workbook.")
MsgBox(0, "", @error & "-" & @extended)
$AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1, "AgentData"); Agent sheet handle
If @error <> 0 Then msgBox(16, "Excel", "Error creating a new Excel Worksheet.")
MsgBox(0, "", @error & "-" & @extended)
_Excel_RangeWrite($OrgChart_OB, $OrgChart_WB, $AgentDataSheet, $headers, 1, 1); Add our headers
MsgBox(0, "", @error & "-" & @extended)

Could you please run the above script? If an error occurres we get more error information.

[Andreu]

Here's how it happens on my end, in order: (I added titles to the msgbox's to assign each their value if they popped.)

NEW WORKBOOK OPENS

ONE: 0-1

TWO: 0-0

Error creating a new Excel Worksheet

THREE: 0-0

FOUR: 3-0

 

Edit:

 

Yea, _Excel_RangeRead completely ignores the Worksheet parameter.

 

I put this in: $OrgChart_WB.Sheets("Sheet4").Activate

 

Right before the RangeRead, worked flawlessly.

Edited June 26, 2013 by Andreu

[water]

When you added _Excel_ErrorNotify(2) does a MsgBox with detailed error information pop up?

[Andreu]

Nope. Only what I sent you came up.

[water]

 

Using a sheet name in SheetAdd causes Extended Error Code 5, but same code... No sheet name = no error. **Adds the sheet, doesn't name it.

My Code: (@Error)

$AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1, "AgentData")

 

My Code: (No Error)

$AgentDataSheet = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1)

 

With "Extended Error Code" do you mean @extended or @error?

If it is @extended = 5, what is the value of @error?

If it is @error = 5 then this is very, very strange. @error = 5 is caused by a COM error. So _Error_Notify(2) should give us a popup with detailed error information.

[Andreu]

With "Extended Error Code" do you mean @extended or @error?

If it is @extended = 5, what is the value of @error?

If it is @error = 5 then this is very, very strange. @error = 5 is caused by a COM error. So _Error_Notify(2) should give us a popup with detailed error information.

I've got to head in to work now. I'll play with it while I'm there.

After reading the UDF... I have to admit my ignorance in how these actually worked. As I already mentioned, I know VB in Excel very very well. Surprising to see how much of it is used in this, and what I could possibly do with it going forward.

I will toy with it at work, and get back to you when I'm able. 

Edit:

It was Extended. I rewrote the code to not use the name portion, but I can recreate the problem.

Edited June 26, 2013 by Andreu

[water]

To solve the _Excel_RangeRead bug simply change line

If IsString($vRange) Then $vRange = $oExcel.Range($vRange)

in function _Excel_RangeRead to

If IsString($vRange) Then $vRange = $oWorksheet.Range($vRange)

[Andreu]

I think I figured out the bug with _Excel_SheetAdd

Local $oSheet = $oWorkbook.WorkSheets.Add($vBefore, $vAfter, $iCount)

Isn't returning anything at all to @Error. This line...

If @error Then Return SetError(5, @error, 0)

Is returning something from prior. I put some absolutely useless function between the two... 

Local $oSheet = $oWorkbook.WorkSheets.Add($vBefore, $vAfter, $iCount)
    Sleep(1)
    If @error Then Return SetError(5, @error, 0)

And there hasn't been one single error. I can pick where I want my sheet (as intended), and what I want to name it. (as intended). That error check in the middle was picking up a false positive and shooting itself in the foot.

Without the useless function = still doesn't work. (Even if you remove the If @error Then Return SetError line).

So that useless function isn't all that useless? I'll keep digging as to why... I'm curious.

Edited June 27, 2013 by Andreu

[Andreu]

But here's a new bug . Now that the sheet names work, I'm testing them completely.

$NewSheet1 = _Excel_SheetAdd($OrgChart_WB, "Sheet2", Default, 1, "Testing1")
$NewSheet2 = _Excel_SheetAdd($OrgChart_WB, Default, "Sheet2", 1, "Testing2")
$NewSheet3 = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1, "Testing3")

$NewSheet4 = _Excel_SheetAdd($OrgChart_WB, "Sheet2", Default, Default, "Testing4")
$NewSheet5 = _Excel_SheetAdd($OrgChart_WB, Default, "Sheet2", Default, "Testing5")
$NewSheet6 = _Excel_SheetAdd($OrgChart_WB, Default, -1, 1, "Testing6")

Apparently, if you use either the $vBefore or the $vAfter parameter, you MUST include the $iCount parameter. Testing 1, 2, 3, and 6 work. Four and 5 do not, and this is the only consistent reason.

Edit:

Fixed. Need to change the syntax to this

Func _Excel_SheetAdd($oWorkbook, $vBefore = Default, $vAfter = Default, $iCount = Default, $sName = Default)

and add this line, much like the other params

If $iCount = Default Then $iCount = 1

Otherwise, trying to use an "optional" parameter behind it (sheet name) will not work. While yes, the user should be able to figure this out on their own... given Default vs Default value of 1 + wanting the latter parameter... But since the rest of the UDF regularly falls back on Default, I myself even fell into that hole. Plus, the less room for error the better right?

-----

Edit: Hope you don't mind but I'm going to be giving this UDF a whole hell of a lot more attention now that I've been curious enough to look. It's so much like working directly in Visual Basic through Excel, which is probably one of my absolute favorite things to use. (I use Excel a lot... seriously.)

------

Edit2: (Then going to bed...)

I've tested every parameter of nearly every function to see if any other breaks could be found. None so far.

Really love it. The speed it handles the amounts of data is quite impressive. There's still a ton of things left me to do, so I'll keep you posted.

By the way, one thing your UDF doesn't provide is any type of formatting. Here's a function I wrote to handle parts of my reports.

I had a lot more to say, but I'm so damn tired... Good night.

expandcollapse popup

Local $headers[1][9] = [["N", "T", "U", "S", "F", "L", "A", "N", "P#"]]; Headers Array
$OrgChart_OB = _Excel_Open(Default, True, Default, True); Excel Object
$OrgChart_WB = _Excel_BookNew($OrgChart_OB); Excel Workbook handle
$NewSheet1 = _Excel_SheetAdd($OrgChart_WB, "Sheet2", Default, 1, "Testing1"); Add sheet
_Excel_RangeWrite($OrgChart_OB, $OrgChart_WB, $NewSheet1, $headers, 1, 1); Add our headers

For $row = 2 To 4
   For $col = 1 To 9
      $OrgChart_WB.Sheets("Testing1").Cells($row, $col).Value = "BLAH"; make some mess 
   Next
Next

_Excel_FontColor($OrgChart_WB, $NewSheet1, "A1:E1", 0xFF0000, 16, "Adobe Hebrew")

; Parameters ....: $oWorkbook - A workbook object. If set to the Excel application object the active workbook of this Excel instance will be used
;                  $oWorksheet- The string for the sheet name.
;                  $vRange    - Does not support R1C1. Must be in the A1:B1 format.
;                  $vColor    - 0xFF0000 format
;                  $vSize     - Optional: Size of the text in the cell/range.
;                  $vFont     - Optional: Name of the font you want. Ie. "Adobe Hebrew"
;                  $vBold     - 1 = Bold Font
;                  $oWrapText - 1 = Wrap text
;                  $vRowHeight- Row Height
Func _Excel_FontColor($oWorkbook, $oWorksheet, $vRange, $vColor, $vSize = Default, $vFont = Default, $vBold = Default, $oWrapText = Default, $vRowHeight = Default)
   If ObjName($oWorkbook, 1) = "_Application" Then $oWorkbook = $oWorkbook.ActiveWorkbook
   If Not IsObj($oWorkbook) Then Return SetError(1, 0, 0)
   If Not IsString($oWorksheet) Then
      For $iIndex2 = 1 To $oWorkbook.WorkSheets.Count
         If $oWorkbook.WorkSheets($iIndex2) = $oWorksheet Then
            $oWorksheet = $oWorkbook.WorkSheets($iIndex2).Name
         EndIf
      Next
   EndIf
   $oWorkbook.Sheets($oWorksheet).Range($vRange).Font.Color=$vColor
   If $vSize <> Default Then $oWorkbook.Sheets($oWorksheet).Range($vRange).Font.Size=$vSize
   If $vFont <> Default Then $oWorkbook.Sheets($oWorksheet).Range($vRange).Font.Name=$vFont
   If $vBold <> Default Then $oWorkbook.Sheets($oWorksheet).Range($vRange).Font.Bold=True
   If $oWrapText <> Default Then $oWorkbook.Sheets($oWorksheet).Range($vRange).WrapText=True
   If $vRowHeight <> Default Then $oWorkbook.Sheets($oWorksheet).Range($vRange).Cells.RowHeight=$vRowHeight

Edited June 27, 2013 by Andreu

[water]

I think I figured out the bug with _Excel_SheetAdd

Local $oSheet = $oWorkbook.WorkSheets.Add($vBefore, $vAfter, $iCount)

Isn't returning anything at all to @Error. This line...

If @error Then Return SetError(5, @error, 0)

Is returning something from prior. I put some absolutely useless function between the two... 

Local $oSheet = $oWorkbook.WorkSheets.Add($vBefore, $vAfter, $iCount)
    Sleep(1)
    If @error Then Return SetError(5, @error, 0)

And there hasn't been one single error. I can pick where I want my sheet (as intended), and what I want to name it. (as intended). That error check in the middle was picking up a false positive and shooting itself in the foot.

Without the useless function = still doesn't work. (Even if you remove the If @error Then Return SetError line).

So that useless function isn't all that useless? I'll keep digging as to why... I'm curious.

Andreu,

thanks for pointing me into the right direction!

The prior function setting @error is StringSplit. If no separator is found then @error is being set. I have already modified the function and hope to release a new version quite soon.

It will be a script breaking change because I changed the number/name of the parameters similar to _Excel_SheetCopyMove. Stay tuned.

[water]

Andreu,

I have updated function _Excel_SheetAdd (and changed parameters).

You should get the following results with the attached test script:

Quote

[0]|13|2
[1]|Testing9.1|
[2]|Testing9.2|
[3]|Testing1|
[4]|Sheet1|
[5]|Testing5|
[6]|Testing2|
[7]|Testing4|
[8]|Testing6|
[9]|Testing7.1|
[10]|Testing7.2|
[11]|Testing3|
[12]|Testing8.1|
[13]|Testing8.2|

expandcollapse popup

#include <excel rewrite.au3>
#include <Array.au3>
Local $headers[1][9] = [["N", "T", "U", "S", "F", "L", "A", "N", "P#"]]; Headers Array
$OrgChart_OB = _Excel_Open(Default, True, Default, True); Excel Object
MsgBox(0, "_Excel_Open", @error & "-" & @extended)
_Excel_ErrorNotify(2)
$OrgChart_WB = _Excel_BookNew($OrgChart_OB); Excel Workbook handle
MsgBox(0, "_Excel_BookNew", @error & "-" & @extended)

$sSheetname = "Sheet1"

$NewSheet1 = _Excel_SheetAdd($OrgChart_WB, $sSheetname, Default, 1, "Testing1")
MsgBox(0, "", "_Excel_SheetAdd before " & $sSheetname & ": " & @error & "-" & @extended)

$NewSheet2 = _Excel_SheetAdd($OrgChart_WB, $sSheetname, False, 1, "Testing2")
MsgBox(0, "", "_Excel_SheetAdd after " & $sSheetname & ": " & @error & "-" & @extended)

$NewSheet3 = _Excel_SheetAdd($OrgChart_WB, -1, False, 1, "Testing3")
MsgBox(0, "", "_Excel_SheetAdd after last sheet" & ": " & @error & "-" & @extended)

$NewSheet4 = _Excel_SheetAdd($OrgChart_WB, Default, Default, Default, "Testing4")
MsgBox(0, "", "_Excel_SheetAdd before active sheet" & $sSheetname & ": " & @error & "-" & @extended)

$NewSheet5 = _Excel_SheetAdd($OrgChart_WB, $sSheetname, False, 1, "Testing5")
MsgBox(0, "", "_Excel_SheetAdd after " & $sSheetname & ": " & @error & "-" & @extended)

$NewSheet6 = _Excel_SheetAdd($OrgChart_WB, -1, True, 1, "Testing6")
MsgBox(0, "", "_Excel_SheetAdd before last sheet" & ": " & @error & "-" & @extended)

$NewSheet7 = _Excel_SheetAdd($OrgChart_WB, -1, True, 2, "Testing7.1|Testing7.2")
MsgBox(0, "", "_Excel_SheetAdd before last sheet 2 sheets" & ": " & @error & "-" & @extended)

$NewSheet8 = _Excel_SheetAdd($OrgChart_WB, -1, False, 2, "Testing8.1|Testing8.2")
MsgBox(0, "", "_Excel_SheetAdd after last sheet 2 sheets" & ": " & @error & "-" &@extended)

$NewSheet9 = _Excel_SheetAdd($OrgChart_WB, 1, Default, 2, "Testing9.1|Testing9.2")
MsgBox(0, "", "_Excel_SheetAdd before first sheet 2 sheets" & ": " & @error & "-" & @extended)

$aSheets = _Excel_SheetList($OrgChart_WB)
_ArrayDisplay($aSheets)

Here is the updated Alpha version of the UDF:

Excel Rewrite.au3

Edited January 24, 2016 by water

[water]

Hope you don't mind but I'm going to be giving this UDF a whole hell of a lot more attention now that I've been curious enough to look. It's so much like working directly in Visual Basic through Excel, which is probably one of my absolute favorite things to use. (I use Excel a lot... seriously.)

I don't mind. I'm grateful for every input that lets us enhance the UDF

 

By the way, one thing your UDF doesn't provide is any type of formatting. Here's a function I wrote to handle parts of my reports.

There are still a lot of functions missing and the existing functions are far from being perfect (e.g. RangeRead/RangeWrite needs to be enhanced).

I haven't yet made up my mind how the many formatting options of Excel can be put into one/multiple functions. The UDF can only have basic functions so people still have to cope with the COM of Excel.

[water]

Poll

Excel has so many formatting options (font, size, color, bold, italic, alignment (horizontal, vertical), ...) that it is impossible to put everything into a single function.

I'm thinking about multiple functions for rows, columns, range content, range format etc.

rows: Height ...

columns: Width ...

range content: font, alignment, number format ...

range format: shading, color, borders ...

What do you think?