We help IT Professionals succeed at work.

I need a (small) VBScript to find Junk characters in the Excel sheet

Venkatkp007
Venkatkp007 asked
on
591 Views
Last Modified: 2009-05-13
We have a spreadsheet upload process through the website and it is stored in one of the folders in the server. We have a separate VBscript in the server which is processing the spreadsheet. few days back we observed that the spreadsheet contained following junk/funny characters

ntent-Transfer-Encoding: 16bit      

hg±í’ß>  
and the entire spreadsheet looked corrupt with above characters. The spreadsheet got corrupted due to some Portal web server settings which we don't have access too.

Do we have a way in VBScript to check if the spreadsheet is corrupt like this? Can anyone guide me?

Pls let me know if you require more information.
Venkatkp
Comment
Watch Question

Commented:
There is a bug in Internet Explorer which can cause corruption of uploaded files (or any multipart/form-data). It is relatively easily prevented by avoiding certain character sets in the HTML of the upload form. For example with UTF-8 Internet Explorer does not have that bug.

ExcelGuideConsultant

Commented:
if you still looking for the VBS then you could try the following (although it will need some modification to meet your need, this is just an example that it is possible)
Dim xlObj, xlWbk
Set xlObj = CreateObject("Excel.application")
Set xlWbk = xlObj.Workbooks.Open("C:\test.xls")
 
For i = 1 to 10 'only checking 10 cells in the first column
'where you see the i you can enter your special characters
If xlObj.ActiveWorkbook.Sheets(1).Range("A" & i & "").Value = "i" Then
Msgbox "I found one!"
End If
Next
 
xlObj.ActiveWindow.Close
xlObj.Quit

Open in new window

Author

Commented:
Psychotec :

I am not a VB Programmer, but i want to check whether if i need to change the logic in the above program to check the junk characters.?

Commented:
> The spreadsheet got corrupted due to some Portal web server settings which we don't have access too.

Do you know which settings? I can not imagine one.

What will you do with corrupted uploaded spreadsheets? Ignore them? That seems the wisest. If they are corrupted your script will produce an error. Catch that error, and ignore the file. Maybe notice the uploader. But if the same file is uploaded again it might get corrupted in the same way.

Why would you want to know how exactly the spreadsheet is corrupted? It seems logical to check if the script can handle the spreadsheet Yes/No. What happens when someone uploads a non-spreadsheet file?

Author

Commented:
sybe

I am just looking for a VB script which will say whether the uploaded spreadsheet is corrupted or not. Nothing else...

Looking for what is happening inside the corrupted sheet is not the solution i am going to do.

Commented:
> I am just looking for a VB script which will say whether the uploaded spreadsheet is corrupted or not.

I am just saying that it is pointless to check if a spreadsheet is corrupted in this very special way that you have defined as 'corrupt'. What if a spreadsheet is corrupted in another way, with different junk characters? Will you come back here and ask how to detect that (newly defined)  corruptedness?

The solution I suggest is simply to catch all errors in handling the uploaded spreadsheet, no matter where they come from. By definition the spreadsheet is 'corrupt' if your script can not handle it (that is: if your script is working correctly).
Consultant
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.