Author Topic: Excel Replace not working  (Read 5408 times)

stanl

  • Pundit
  • *****
  • Posts: 939
Excel Replace not working
« on: July 21, 2013, 05:51:54 am »
Wanted to quickly replace all cells in activesheet that contain negatives with 0.  This macro works:
Code: [Select]
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
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

  • Full Member
  • ***
  • Posts: 184
  • WinBatch rocks, and so does Wilson WindowWare!
    • WizardWrx
Re: Excel Replace not working
« Reply #1 on: July 21, 2013, 07:43:14 pm »
What's with the double colon after the word Replace?
David A. Gray
You are more important than any technology.

stanl

  • Pundit
  • *****
  • Posts: 939
Re: Excel Replace not working
« Reply #2 on: July 22, 2013, 01:58:33 am »
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

  • Pundit
  • *****
  • Posts: 939
Re: Excel Replace not working
« Reply #3 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.

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Excel Replace not working
« Reply #4 on: July 22, 2013, 09:00:14 am »
Stan,

In your last post you mentioned that this worked:
Quote
oWS.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
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

  • Pundit
  • *****
  • Posts: 939
Re: Excel Replace not working
« Reply #5 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.

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Excel Replace not working
« Reply #6 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.
Deana F.
Technical Support
Wilson WindowWare Inc.

stanl

  • Pundit
  • *****
  • Posts: 939
Re: Excel Replace not working
« Reply #7 on: July 22, 2013, 11:18:05 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.