GreenCan

issue with _Excel_RangeFind ?

7 posts in this topic

#1 ·  Posted (edited)

I am trying to find a way to _Excel_RangeFind a cell format but I don't succeed in finding a proper way.

Please run the example script first, columns B and C are relevant for the test case. Disregard columns D to G.

#include <Excel.au3>
#include <Array.au3>


Global $sTimeLocale = RegRead("HKCU\Control Panel\International", "sShortTime")
ConsoleWrite("Locale time format on this PC: " & $sTimeLocale & @CRLF)


Global $oExcel = _Excel_Open();$bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bInteractive = Default, $bForceNew = Default)
Global $oWorkbook = _Excel_BookNew($oExcel)


; ***************************************************************
; Example - Format Numbers
; *****************************************************************
Global $sTimeformat = "[u]:mm"
Global $aFormatExamples[1][7] = [["Format Examples", $sTimeformat, $sTimeformat, "###0,00", "$#.##0,00", "€ #.##0,00;[Red]€ -#.##0,00", "General"]];Array to Create Headers, European format example
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aFormatExamples, "A1")

; Fill-up some cells with random Numbers
Global $iRows= 15
Global $aArray2D[$iRows +6][UBound($aFormatExamples, 2) + 1]

For $i = 4 To $iRows - 2
    $aArray2D[$i][0] = Random(0, 1000)
Next

For $i = 1 To $iRows + 5
    $aArray2D[$i][1] = Random(0, 1000)
Next
For $j = 2 To UBound($aFormatExamples, 2) - 2
    For $i = 2 To $iRows + 5
        $aArray2D[$i][$j] = Random(-1000, 1000);this time allow negative numbers
    Next
Next
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aArray2D, "B2")

MsgBox(0, "Formating example", "Applying format in the green area only")

$oExcel.Activesheet.Range("B4:G16").Interior.Color = 0x3AFF00 ; color range green

; Set each column to its type of Format
$oExcel.ActiveSheet.Range("B4:B15").NumberFormat = $aFormatExamples[0][1]
$oExcel.ActiveSheet.Range("C4:C15").NumberFormat = $aFormatExamples[0][2]
$oExcel.ActiveSheet.Range("D4:D15").NumberFormat = $aFormatExamples[0][3]
$oExcel.ActiveSheet.Range("E4:E15").NumberFormat = $aFormatExamples[0][4]
$oExcel.ActiveSheet.Range("F4:F15").NumberFormat = $aFormatExamples[0][5]
$oExcel.ActiveSheet.Range("G4:G15").NumberFormat = $aFormatExamples[0][6]

;AutoFits
$oExcel.Columns.AutoFit
$oExcel.Rows.AutoFit

MsgBox(0, "Formating example", "Set sums")
Global $aSums[1][7] = [["Range starting  Row 3","=SUM(B3:B23)", "=SUM(C3:C23)", "=SUM(D3:D23)", "=SUM(E3:E23)", "=SUM(F3:F23)", "=SUM(G3:G23)"]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aSums, "A24")

Global $aSums[1][7] = [["range starting  Row 4","=SUM(B4:B23)", "=SUM(C4:C23)", "=SUM(D3:D23)", "=SUM(E3:E23)", "=SUM(F3:F23)", "=SUM(G3:G23)"]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet,  $aSums, "A25")

;AutoFits
$oExcel.Columns.AutoFit
$oExcel.Rows.AutoFit


MsgBox(0, "test", "RangeFind format?")

; full match
$aResult =_Excel_RangeFind($oWorkbook, $sTimeformat, "B3:B23", $xlFormulas, $xlPart); , $iLookAt = Default, $bMatchcase = Default)
ConsoleWrite(@ScriptLineNumber & " " & @error & @CR)
_ArrayDisplay($aResult, @ScriptLineNumber)
; partial match
$aResult =_Excel_RangeFind($oWorkbook, ":mm", "B3:B23", $xlFormulas, $xlPart); , $iLookAt = Default, $bMatchcase = Default)
ConsoleWrite(@ScriptLineNumber & " " & @error & @CR)
_ArrayDisplay($aResult, @ScriptLineNumber)

If MsgBox(4 + 0, "Formating example", "Done, close without save ") = 6 Then
    _Excel_BookClose($oWorkbook, False)
     _Excel_Close($oExcel)
EndIf

The format is put to ':mm' in the green area only
Please check if this format is appropriate for your system, might need to change this to '[h]:mm' as pr your Locale Excel settings (line 16)

Case 1: when summing up the columns as from row 3, Excel considers the sum for the range B3:B23/C3:C23 to be in format 'General'
Case 2: when summing up the columns as from row 4, Excel considers the sum for the range B4:B23/C4:C23  to be in format ':mm'

This is an expected behavior of Excel, although one might think that at least for column B, there is no ambiguity, the sum should be in :mm, but OK...

I now want to search for the column range if I can find cells in :mm format, and according to the result, I can reformat the sum in the appropriate number format.

In VB:

Sub FindNumberFormat()
'
'  FindNumberFormat
'  run multiple times to find the next cell in the format...
'
    Application.FindFormat.NumberFormat = "[h]:mm"
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
End Sub

In AutoIt however, nothing is found and

$aResult =_Excel_RangeFind($oWorkbook, $sTimeformat, "B3:B23", $xlFormulas, $xlPart)

returns an empty array.

I am confused with the VB statement 'Application.FindFormat.NumberFormat = "[h]:mm"' that I find nowhere in the  _Excel_RangeFind() function.

Has anyone tested the function already?

Thank you (...water ;) )

PS. I mostly try to find a solution myself, but sometimes, I have to look at the real specialists...

Edited by GreenCan

Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites



Hi Greencan,

Will have a look at it as soon as I return from vacation :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Hi Greencan,

Will have a look at it as soon as I return from vacation :)

​Water, your vacation must have been pretty long... :lol:


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Opps,
looks like I missed to solve this problem. Will have a look at it the next days when the weather gets bad so I do not have to sit on my racing bike ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Don't worry. I will be on my bike the whole next week, touring around in France, scheduled 1.000 km at tourist speed...:lmao:

Cheers


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

Have a nice week and and enjoy France. I wish you perfect weather for biking :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

I had a quick look at the problem.
Result: _Excel_RangeFind does not support looking for formats at the moment.

Solving the current problem shouldn't be too hard. It will take some time but I hope to come up with a solution quite soon ...


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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