kcvinu

Excel Range find function giving bad results

6 posts in this topic

#1 ·  Posted (edited)

Hi all,

I have used _Excel_RangeFind function to find current date from an excel sheet. Dates in my excel sheet is lokks like this "19-04-2015". But the function displays the results like this "20150419000000". Please see the attached image. So i need to use extra code for stripping all those unwanted zeros and put a hyphen between the date value. Why this is happening. Do i need to care anything to prevent this ?.

Here is my code

Local $Output = _Excel_RangeFind($WBook,"19-04-2015","J3:R24") 

And look at the picture, Name of the cell is empty. And address of the cell contained a dollar sign. Can i use this address in another location of this program to find this cell ?

Final question. I need to do some task when a GUI form load event happen. Help file is saying about only these events

1. $GUI_EVENT_MAXIMIZE

2. $GUI_EVENT_RESTORE

3. $GUI_EVENT_MINIMIZE

4. $GUI_EVENT_CLOSE

Where can i found $GUI_EVENT_FORM_LOAD

post-89644-0-56627000-1429437238_thumb.j

Edited by kcvinu

My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites



The reason is that you are retrieving the 'values' as stored by excel if you want the want the data retrieved to have the same format as displayed in excel you have to retrieve the data as 'text' which returns the data as displayed by excel.

Local $Output = _Excel_RangeFind ( $WBook, "19-04-2015","J3:R24", xlText)

 


"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

@

Bowmore

Error. xlText is undefined function

$xlText is an undeclared global variable. My computer dindn't know this "xlText" 


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

Sorry my mistake.

This slightly modified version of _Excel_RangeFind by water will give you the required information. What I have done is add an extra column to the output containing the excel formatted output in the last column.

; #FUNCTION# ====================================================================================================================
; Author ........: water
; Modified.......: Bowmore
; ===============================================================================================================================
Func _Excel_RangeFindEx($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Parent.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][7], $iIndex = 0, $iIndexSheets = 1       ;<==== changed [6] to [7]
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Name.Name
                $aResult[$iIndex][2] = $oMatch.Address
                $aResult[$iIndex][3] = $oMatch.Value
                $aResult[$iIndex][4] = $oMatch.Formula
                $aResult[$iIndex][5] = $oMatch.Comment.Text
                $aResult[$iIndex][6] = $oMatch.Text          ;<==== Added .text column
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][7] ;<==== changed [6] to [7]
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex + 1][7] ;<==== changed [6] to [7]
    Return $aResult
EndFunc   ;==>_Excel_RangeFind
1 person likes this

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

@

Bowmore

Thanks. And where did you get this function ?. Please let me know. 


My Contributions

UDF Link Viewer   --- A tool to visit the links of some most important UDFs 

 Includer_2  ----- A tool to type the #include statement automatically 

 Digits To Date  ----- date from 3 integer values

PrintList ----- prints arrays into console for testing.

 Alert  ------ An alternative for MsgBox 

 MousePosition ------- A simple tooltip display of mouse position

GRM Helper -------- A littile tool to help writing code with GUIRegisterMsg function

Access_UDF  -------- An UDF for working with access database files. (.*accdb only)

 

Share this post


Link to post
Share on other sites

@

Bowmore

Thanks. And where did you get this function ?. Please let me know. 

Its just the standard _Excel_RangeFind() function from the Excel.au3 UDF include that I have slightly modified.


"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

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

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • Mag91
      By Mag91
      Hey Community,
      cause im too new in the Auto it world i will try it with the your help. hopefully.
      I woud like to know how i can handle my Problem.
      ----
      I have a Excel Data with 362 random numbers.
      For Example:
      1166642335374 1172899897343
      .....
      this numbers are a part of the filepath ...example
      D:\Projekte\1166_64233_5374
      as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_"
      This is my first question. How can i handle this to make it Shell execute.
       
      --------
      Second question:
      If i am in the path.
      For Example:
      D:\Projekte\1166_64233_5374
      the code shoud search for specific PDF Files.
      They are named like: 0050569E364B1ED79B900F73E62660EC.pdf
      the first 15 letters are always the same
      0050569E364B1ED
      when he found this data he has to copy it on a Folder on the Desktop.
      (There can also be 2 or 3 pdfs in one Folder with this letters)
      ----
      Please give me some help :-)