vba - Excel file contains invalid hidden characters that can't be removed -
i have peculiar problem hidden characters in excel spreadsheet uses vba create text file. i've attached link test version of file, , i'll explain best can issue.
the file creates plain txt file can used feed data system use. works normally, we've been supplied approximately 15,000 rows of data, , @ random points throughout there hidden characters.
in test file, there's 1 row , it's cell b11 has hidden characters @ beginning , end of value. if put cursor @ end of it, , press backspace key, if nothing has happened, you've deleted 1 of characters.
as far excel concerned, hidden characters question marks, they're not, text stream parse those, doesn't, , instead throws invalid procedure call error.
i've tried using excel's clean formula, i've tried vba equivalent, tried using 'replace', nothing seems recognise characters. excel convinced they're question marks, ascii character call gives me same answer (63), replace doesn't replace them question marks, omits them!
any on this, if it's formula apply appreciated. in interests of data protection data in file fake way, it's nobody's real ni number.
this vba macro run on own or in conjunction clearformatting macro. did strip out rogue unichars sample.
sub strip_rogue_unichars() dim uc long cells(11, 1).currentregion uc = 8000 8390 .replace what:=chrw(uc), replacement:=vbnullstring, lookat:=xlpart doevents next uc end end sub there's better way , being able restrict scope of unicode characters search , replace speed things up. turning off .enableevents, .screenupdating, etc likewise help. believe calculation @ manual. intentionally left doevents in loop first run several thousand different unichars.
Comments
Post a Comment