"Unable to get clean property of the Worksheetfunction Class"

[JasperD]

Hi guys and girls,

using a MSXML2.XMLHTTP method, I get a website responsetext that I store in a s:tring.
This string (dimmed as "pres") contains crap like enters and tabs, that I want to remove by using the clean function.
If I store the first 32000 characters of the string in a cell (due to cell length limitation only 32000 characters), then use

presclean = Application.Worksheetfuction.Clean(Trim(Range("A1").Value)))

it works fine.

However, I need to use the clean/trim function without storing the responsetext to a cell first.
If I do this :

pres = pol.responsetext
presclean = Application.WorksheetFunction.Clean(Trim(pres))

it gives me an error : "Unable to get clean property of the Worksheetfunction Class" and I have no clue why.
Any bright minds who could help me on this?

Thank you so much in advance!

[JosephP]

how many characters is pres?

[JasperD]

It varies each time when I get the website responsetext (it's product information, which is retrieved based on EAN code or part number).
In the two example I'm trying to get to work, it's 52406 characters for one product and 76544 for the other....

[ragulduy]

I guess you need something like:

for count = 1 to len(pres) step 32000
presclean = presclean & application.worksheetfunction.clean(trim(mid(pres,count,32000)))
next

[JasperD]

Hi yudlugar,

this works great, thank you - after seeing your solution I realized that I need to keep the character limitation in mind even though I don't actually do anything on the worksheet, I still use a WORKSHEET_FUNCTION.

A bit silly of me to overlook it in the first place and thank you for helping me realize it !

All the best,

Jasper