Excel Replace not working

Started by stanl, July 21, 2013, 05:51:54 AM

Previous topic - Next topic

stanl

Wanted to quickly replace all cells in activesheet that contain negatives with 0.  This macro works:

Sub minus()
Set oWS = ActiveSheet
oWS.UsedRange.Select
Selection.Replace What:="-*", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub



when modified for WB I get OLE error
Code (WINBATCH) Select

xlPart = 2
xlByRows = 1

...open any workbook with negative numbers, set oWS to a worksheet
oWS.UsedRange.Select()
oXL.Selection.Replace(::What="-*",Replacement="0",LookAt=xlPart,SearchOrder=xlByRows,MatchCase=@False,SearchFormat=@False,ReplaceFormat=@False)   


What am I missing? I purposely set the macro to be as close to possible as the WB code.

DAG_P6

What's with the double colon after the word Replace?
David A. Gray
You are more important than any technology.

stanl

Quote from: DAG_P6 on July 21, 2013, 07:43:14 PM
What's with the double colon after the word Replace?
WB's way of handling positional parameters. Start with :: and remove the single : in each parm. At least that is how I have used positional parms in the past.

stanl

....really needed this for work, WTF: this worked
oWS.Cells.Replace(::What="-*",Replacement="0")

seems WB had issues with extended parameters (in this case) while the macro did not.

Deana

Stan,

In your last post you mentioned that this worked:
QuoteoWS.Cells.Replace(::What="-*",Replacement="0")
. That code seems to use the Cells object as opposed to the Selection object. WinBatch should be able to specify all of the other positional parameters. Maybe give this a try:
Code (winbatch) Select

xlPart = 2
xlByRows = 1

...open any workbook with negative numbers, set oWS to a worksheet
oWS.Cells.Replace(::What="-*",Replacement="0", LookAt=xlPart, SearchOrder=xlByRows, MatchCase=@False, SearchFormat=@False, ReplaceFormat=@False)


If you still get an error. Simply add DebugTrace(@on,"trace.txt") to the beginning of the script and inside any UDF, run it until the error or completion, then inspect the resulting trace file for clues as to the problem. Feel free to post the trace file here ( removing any private info) if you need further assistance.
Deana F.
Technical Support
Wilson WindowWare Inc.

stanl

Deana;

I posted that with about 5 minutes left to head out to work. Either Cells or Used Range work, neither work with extended parms. DebugTrace doesn't help, it is a generic OLE error variable/assignment.

In the past I have successfully called multiple parms with Excel's add(), move() and pivot functions. This is work-related and I cannot share the data, but I also tested on a quick worksheet I put together with some negative numbers. The code failed with multiple parms, but succeeded with my fix. I also tried using numerics for xlbyrows,xlpart and @False - same outcome.

Deana

Stan,

There is a code sample posted in the tech database, that uses those named parameters :
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Excel~Replace~String.txt

Apparently the SearchFormat and ReplaceFormat that are passed to the Replace method, want object handles returned from the FindFormat and ReplaceFormat properties.
Deana F.
Technical Support
Wilson WindowWare Inc.

stanl

Quote from: Deana on July 22, 2013, 10:14:28 AM
Stan,

There is a code sample posted in the tech database, that uses those named parameters :

I missed that, I searched for the work 'Replace'... should have searched for the keyword. Otherwise, using the default as I did works fine as the Excel data is merely an ASCII import with no special formatting, so I assume the defaults are fine.

Thanks for citing the Tech DB reference.