Issue with tab delimited file, text seen as formulas

[trip.turlington]

I have an application on a midrange server that generates a tab delimited file, then invokes a macro-enabled Excel file that I store on that server's integrated file system. That macro-enabled file then saves the generated tab delimited file as a formatted report to the user's client PC. This way, the formatting is uniform and the user doesn't have to worry about it, and I can easily maintain how those files are formatted in to easy to read reports.

Recently I was given this task for a new report, but one of the fields coming in from the tab delimited file generated by the server application contains special characters. It is simply a comment field, but occasionally our users will refer to part numbers or whatever that also includes special characters, and sometimes those special characters end up being the first character in the string. So, whenever I come across a leading character that Excel uses for a formula (like - or =), I suddenly no longer have my comment. My problem, is that I can't catch Excel to stop it from automatically taking comments with - or = as a leading character and trying to make them in to some formula.

My question is, in VBA, is there a way to set my macro upon execution to stop Excel from trying to automatically make formulas from text with leading hyphens/equals signs, or format the entire sheet to text BEFORE Excel loads the file? I would rather do this than modify the server application to substring special characters (so the users can see comments as they were entered), but if there isn't a way to do so, then I guess that's what I'll have to do.

So far I have tried:
1) application.calculation=xlmanual
2) Creating a new sheet and formatting all cells to text, then copying from the sheet that opens from the tab delimited file to the new sheet
3) Running a macro in the open routine that mimicks the import text file solution I found here: http://www.mrexcel.com/forum/showthread.php?t=42427
4) An ancient Shaman prayer that somehow got me hopelessly lost in the astral plane for about 4 days.

I am hoping one of you experts know of a command or option I can just set on or off, but as I said before if I can't do it and have to modify the server side program, then so be it. But if there is something like application.autoformula=xlstopdoingthis that I can just add in my macro, that would be WONDERFUL.

Thanks in advance for any and all replies. Oh, and we are using Excel 2007, if that matters.

~Trip

 

[trip.turlington]

Actually, I couldn't wait and tested it on my little test case on my desktop. Modifying the array value for the comment column WORKED! THANK YOU p45cal!