Search and replace Calc in vbscript

[minhnj]

I want to search and replace "#REF!" or "#N/A" to 0 in openoffice cal in vbscript.

I run into error at line: Set ReplaceDescriptor = Sheet.createReplaceDescriptor(). It errors "Object does not support this property or method: Sheet.createReplaceDescriptor()"

I can't figure out why. Any help is greatly appriciated.

Code: Select all

'open CSV file
cCSVURL = "csv file path here"

Set oCSVDoc = StarDesktop.loadComponentFromURL(cCSVURL, "_blank", 0, Array(MakePropertyValue("Hidden", True)))

Set objServiceManager = WScript.CreateObject("com.sun.star.ServiceManager") 
Set StarDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop") 

Set oComponents = StarDesktop.getComponents() 
Set oEnum = oComponents.createEnumeration 

Do While oEnum.hasMoreElements 
    Set ThisComponent = oEnum.nextElement 
    If ThisComponent.supportsService(" com.sun.star.util.XSearchDescriptor") Then Exit Do 
Loop 

Set oDoc = ThisComponent 
Set Sheet = oDoc.Sheets(0)
Set ReplaceDescriptor = Sheet.createReplaceDescriptor()
ReplaceDescriptor.SearchString = "#N/A"
ReplaceDescriptor.ReplaceString = "0"
Sheet.ReplaceAll(ReplaceDescriptor)

ReplaceDescriptor.SearchString = "#REF!"
ReplaceDescriptor.ReplaceString = "0"
Sheet.ReplaceAll(ReplaceDescriptor)

're-save CSV
oCSVDoc.saveAsURL cCSVURL,Array(MakePropertyValue("FilterName", "Text - txt - csv (StarCalc)"),MakePropertyValue("Overwrite", True))

oCSVDoc.Close(True)


	
StarDesktop.terminate
'objServiceManager.dispose()

Set objServiceManager = Nothing
Set StarDesktop = Nothing

[FJCC]

com.sun.star.util.XSearchDescriptor is not a Service, so your While loop simply runs through all the open components and ThisComponent ends up being whichever component was tested last. Another problem is that you try to open the csv file before you have defined the StarDesktop variable, so that must fail. If you move the line

Code: Select all

Set oCSVDoc = StarDesktop.loadComponentFromURL(cCSVURL, "_blank", 0, Array(MakePropertyValue("Hidden", True)))

to a point after StarDesktop is defined, then oCSVDoc can refer to a spreadsheet document and you won't have to search through the open documents to find it. To ensure that the csv is opened as a Calc document, you may have to choose the appropriate filter as part of the call to loadComponentFromURL().