Another Excel Post

[uncommon]

I have a lot of common names in a excel list I want to remove. My current technique is using a find and replace function I found in a UDF….

#include <ExcelCOM_UDF.au3>
…
Sleep(1000)
$oExcel = _ExcelBookOpen(@UserProfileDir & "\Dropbox\link to me\Market Scrapper\OUTPUT\stocknames.csv")
;-------------------------------------------------->read number of cells
_ExcelWriteCell($oExcel, "=COUNTA(A1:A10000)", 1, 13)
Sleep(100)
$numnames = _ExcelReadCell($oExcel, 1, 13)
Sleep(500)
_ExcelWriteCell($oExcel, "", 1, 13)
;-------------------------------------------------->Read Number of other cells
_ExcelWriteCell($oExcel, "=COUNTIF(D1:D10000," & Chr(34) & "orange" & Chr(34) & ")", 2, 13)
Sleep(100)
$orgnames = _ExcelReadCell($oExcel, 2, 13)
Sleep(500)
_ExcelWriteCell($oExcel, "", 2, 13)
;--------------------------------------------------


_ExcelReplaceInRange($oExcel,"et al", "", 1, 1, $numnames, 1);remove all "et al"
_ExcelReplaceInRange($oExcel,".", "", 1, 1, $numnames, 1);remove all "."
_ExcelReplaceInRange($oExcel,",", "", 1, 1, $orgnames, 1);remove "," for all names under ornage county
;_ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1,$iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False, $fReplaceFormat = False)

The _ExcelReplaceInRange function only replaces the txt it within the criteria. If there was a way I could replace the whole cell as a blank, that would fix my problem. Other suggestions?

I searched around for my questions before posting here, but if I missed it, point me in the right direction please.

[Juvigy]

You can try writing the value " " to the cell. You can also copy and paste from a blank cell.

[wyzzard]

You can try writing the value " " to the cell. You can also copy and paste from a blank cell.

I don't think that will work in this case as you don't necessarily know which cells match the criteria specifically in the range it does the replacement on.

I haven't done any Excel functions and can't test them at home since I only have OpenOffice here, but would putting the "*" wildcard character before and after what you are doing the search on fix that maybe?

Otherwise what you could do is take the UDF function, find the part of the code that does the partial replace and modify it to just make the cell blank instead of doing a string replace.

expandcollapse popup

;===============================================================================
;
; Description:   Finds all instances of a string in a range and replace them with the replace string.
; Syntax:        _ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
; $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False, $fReplaceFormat = False)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $sFindWhat - The string to search for
; $sReplaceWith - The string to replace the search string with
; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
; $iColStart - The starting column for the number format(left) (default=1)
; $iRowEnd - The ending row for the number format (bottom) (default=1)
; $iColEnd - The ending column for the number format (right) (default=1)
; $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
; $fMatchCase - Specify whether case should match in search (True or False) (default=False)
; $fMatchFormat - Specify whether cell formatting should match in search (True or False) (default=False)
; $fReplaceFormat - Specify whether cell format will be reset (True Or False) (default=False)
; Requirement(s): AutoIt Beta 3.2.1.12
; Return Value(s): On Success - Returns 1
;                On Failure - Returns 0 and sets @error on errors:
; @error=1 - Specified object does not exist
; @error=2 - Starting row or column invalid
; @extended=0 - Starting row invalid
; @extended=1 - Starting column invalid
; @error=3 - Ending row or column invalid
; @extended=0 - Ending row invalid
; @extended=1 - Ending column invalid
; @error=4 - Whole or part parameter invalid
; Author(s):     SEO <locodarwin at yahoo dot com>, many thanks to DaLiMan
; Note(s):       None
;
;===============================================================================
Func _ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "", $fReplaceFormat = False)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iWholeOrPart < 1 Or $iWholeOrPart > 2 Then Return SetError(4, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
If $iColStart < 1 Then Return SetError(2, 1, 0)
If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
Else
$oExcel.Range($sRangeOrRowStart).Select
EndIf
If $fMatchFormat = "" Then
$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, Default, $fReplaceFormat)
Else
$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)
EndIf
Return 1
EndFunc ;==>_ExcelReplaceInRange

I'm thinking you need the change the $oExcel.Selection.Replace command with a plain cell write command to have it make it blank or modify it in some other fashion to make it work. Don't know what the format for that is exactly so you'll have to look that up.

Hopefully it's something to help you head in the direction you need.

Okay... found the following VBA code -

Dim rCell As Range
For Each rCell In Selection
If Not InStr(1, rCell.Text, "test", vbTextCompare) = 0 Then
rCell.Value = ""
End If
Next

End Sub

So I'm thinking you modify it into Autoit format and do an If/Then using the Replace command as the test then do a cell write of "" if it matches your test parameters.

Sorry I can't actually give you code that does this, Still new at Autoit.

Edited September 10, 2012 by wyzzard

[Reg2Post]

If StringInStr($numnames, "et al") <> 0 Then _ExcelWriteCell($oExcel, "", 1, 13)

Replace _ExcelReplaceInRange lines with above. Modify accordingly for $orgnames var or others as needed.

EDIT: I just looked back at the first post and noticed that the string search is being done on a cell with a numerical value. A loop will be needed to go through all the cells in a range.

Edited September 10, 2012 by Reg2Post

[uncommon]

I don't think that will work in this case as you don't necessarily know which cells match the criteria specifically in the range it does the replacement on.

I haven't done any Excel functions and can't test them at home since I only have OpenOffice here, but would putting the "*" wildcard character before and after what you are doing the search on fix that maybe?

Otherwise what you could do is take the UDF function, find the part of the code that does the partial replace and modify it to just make the cell blank instead of doing a string replace.

expandcollapse popup

;===============================================================================
;
; Description:   Finds all instances of a string in a range and replace them with the replace string.
; Syntax:        _ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
; $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False, $fReplaceFormat = False)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $sFindWhat - The string to search for
; $sReplaceWith - The string to replace the search string with
; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
; $iColStart - The starting column for the number format(left) (default=1)
; $iRowEnd - The ending row for the number format (bottom) (default=1)
; $iColEnd - The ending column for the number format (right) (default=1)
; $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
; $fMatchCase - Specify whether case should match in search (True or False) (default=False)
; $fMatchFormat - Specify whether cell formatting should match in search (True or False) (default=False)
; $fReplaceFormat - Specify whether cell format will be reset (True Or False) (default=False)
; Requirement(s): AutoIt Beta 3.2.1.12
; Return Value(s): On Success - Returns 1
;                On Failure - Returns 0 and sets @error on errors:
; @error=1 - Specified object does not exist
; @error=2 - Starting row or column invalid
; @extended=0 - Starting row invalid
; @extended=1 - Starting column invalid
; @error=3 - Ending row or column invalid
; @extended=0 - Ending row invalid
; @extended=1 - Ending column invalid
; @error=4 - Whole or part parameter invalid
; Author(s):     SEO <locodarwin at yahoo dot com>, many thanks to DaLiMan
; Note(s):       None
;
;===============================================================================
Func _ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "", $fReplaceFormat = False)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iWholeOrPart < 1 Or $iWholeOrPart > 2 Then Return SetError(4, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
If $iColStart < 1 Then Return SetError(2, 1, 0)
If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
Else
$oExcel.Range($sRangeOrRowStart).Select
EndIf
If $fMatchFormat = "" Then
$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, Default, $fReplaceFormat)
Else
$oExcel.Selection.Replace($sFindWhat, $sReplaceWith, $iWholeOrPart, Default, $fMatchCase, Default, $fMatchFormat, $fReplaceFormat)
EndIf
Return 1
EndFunc ;==>_ExcelReplaceInRange

I'm thinking you need the change the $oExcel.Selection.Replace command with a plain cell write command to have it make it blank or modify it in some other fashion to make it work. Don't know what the format for that is exactly so you'll have to look that up.

Hopefully it's something to help you head in the direction you need.

Okay... found the following VBA code -

Dim rCell As Range
For Each rCell In Selection
If Not InStr(1, rCell.Text, "test", vbTextCompare) = 0 Then
rCell.Value = ""
End If
Next

End Sub

So I'm thinking you modify it into Autoit format and do an If/Then using the Replace command as the test then do a cell write of "" if it matches your test parameters.

Sorry I can't actually give you code that does this, Still new at Autoit.

Adding a wild card character or number at the beginning would work, but _ExcelReplaceInRange only modify the criteria.

I'll look your UDF tonight...

[uncommon]

If StringInStr($numnames, "et al") <> 0 Then _ExcelWriteCell($oExcel, "", 1, 13)

Replace _ExcelReplaceInRange lines with above. Modify accordingly for $orgnames var or others as needed.

EDIT: I just looked back at the first post and noticed that the string search is being done on a cell with a numerical value. A loop will be needed to go through all the cells in a range.

Yeah I know I can accomplish the work using a loop, but it will take longer as I have 10,000 or so names to go through. _ExcelReplaceInRange but only does partial text. That's the problem.

[Reg2Post]

Yeah I know I can accomplish the work using a loop, but it will take longer as I have 10,000 or so names to go through. _ExcelReplaceInRange but only does partial text. That's the problem.

All suggestions in this post will require a loop, even modifying the _ExcelReplaceInRange UDF. And just off the top of my head I think you can accomplish what you want with less than a dozen new lines (including the loop).

- Find last non-empty cell in range

- Use last non-empty cell value for counter upper value

- Loop through and check each used cell with the if statement posted (Hint: use counter var for cell location)

[uncommon]

What the hell are these things called...?

--------->$oExcel.Selection.Replace<-------

The ".Selcetion" and ".Replace" I dont know what to look for in the help file?

[Myicq]

What the hell are these things called...?

--------->$oExcel.Selection.Replace<-------

The ".Selcetion" and ".Replace" I dont know what to look for in the help file?

"these things" are properties and methods of the object (in this case $oExcel). Since $oExcel is an Excel Application object, it has the same properties and methods as Excel.

Obviously, the AutoIT help file can't include documentation for all objects you choose to use, so why not look in Excel developer reference, perhaps starting out at Application Object ? This is a HUGE thing with LOTS of material to read.

Best way to "learn" - at least for me - is to use the macro recorder in Excel. Record a macro, do something you want to automate, and view the macro afterwards in the VBA editor. That way, you can pretty much see how to. Compare with examples here, to learn how to go from VBA notation to "AutoIT" notation. The difference is small.

[water]

That's nothing you find in the AutoIt help file. You have to look at the MSDN Excel COM documentation.

For Excel 2007: Range, Selection and Replace.