WinBatch® Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: stanl on July 21, 2013, 05:51:54 AM

Title: Excel Replace not working
Post by: stanl on July 21, 2013, 05:51:54 AM
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.
Title: Re: Excel Replace not working
Post by: DAG_P6 on July 21, 2013, 07:43:14 PM
What's with the double colon after the word Replace?
Title: Re: Excel Replace not working
Post by: stanl on July 22, 2013, 01:58:33 AM
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.
Title: Re: Excel Replace not working
Post by: stanl on July 22, 2013, 03:54:23 AM
....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.
Title: Re: Excel Replace not working
Post by: Deana on July 22, 2013, 09:00:14 AM
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.
Title: Re: Excel Replace not working
Post by: stanl on July 22, 2013, 09:53:24 AM
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.
Title: Re: Excel Replace not working
Post by: 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 :
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.
Title: Re: Excel Replace not working
Post by: stanl on July 22, 2013, 11:18:05 AM
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.