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

 

[p45cal]

Yes, importing as text in step 3 of the import wizard it likely to be the way to go. What's the current code that imports the file look like? - it's probably just a tweak.

 

[kpark91]

I know this is not really what you're looking for but I have two solutions taht could work as I lack some knowledge in imports/..
1) Execute a task same as pressing Ctrl + ` on the keyboard in Excel
2) add =" * " when importing the files where * is the content of the data. This will make all the data show and also make them all text.

 

[trip.turlington]

What's the current code that imports the file look like? - it's probably just a tweak.

Thank you! This is what recording the macro gave me:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\turlington\Desktop\NewJobsList.txt", _
        Destination:=Range("$A$1"))
        .Name = "NewJobsList"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

As I'm a programmer just getting in to learning VBA, I've been learning by recording macros and then analyzing the code, and spending a lot of time lurking on here. This is the first problem I've had that has tripped me up to the point where I just plain couldn't find a solution, here or otherwise. I know that recording macros gives you a bunch of default code for settings you don't really need, but I purposely haven't tried to trim this down yet until I get a chance to start looking at what everything does, and then eliminate what I don't need. (I'm kind of a neat freak when it comes to coding)

I know this is not really what you're looking for but I have two solutions taht could work as I lack some knowledge in imports/..
1) Execute a task same as pressing Ctrl + ` on the keyboard in Excel
2) add =" * " when importing the files where * is the content of the data. This will make all the data show and also make them all text.

Thank you for the reply!
1) I tried the CTRL+`, but then I lose the formatting for my date fields as well?
2) THAT worked on my little test file and macro-workbook on my desktop. It would still require changing the server application, BUT it would allow me to display the comments as they were written and eliminates substringing or parsing. If there is no way to tweak the above code, then I'll accept this as my solution. Thank you!

 

[kpark91]

While p45cal is working on tweaking the code.
I forgot to mention that you will most likely need to add an extra " if there is " in the data.
As you need double double quotation marks in order to display one single double quotation mark in a text formula (="")

For example, to display one single double quotation mark,

=""""

 

[trip.turlington]

So, if I need to display quotes in a text field, I need to have my server program output this to the tab delimited file:

=""I need to quote someone""

In order for Excel to display: "I need to quote someone"

whereas...

="I do not need to quote someone"

In order for Excel to display: I do not need to quote someone

Am I following you correctly?

 

[kpark91]

Wait no.

In order for Excel to display: "I need to quote someone"

="""I need to quote someone"""

 

[trip.turlington]

Sweet, thank you very much. I'm hoping that code above can be tweaked, but if not, then thank you very much for the alternative!

 

[p45cal]

It's going to centre on tewaking:
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

The array is the columns, left to right, and the number represents how to import that column according to:
1, Format General
2, Text
9, Skip column
4, Day-Month-Year date format
7, Day-Year-Month date format
10, EMD date
3, Month-Day-Year date format
6, Month-Year-Day date format
8, Year-Day-Month date format
5, Year-Month-Day date format

So it's likely you'll want to change a few of those 1s to 2s. I don't know what's where so I leave that to you.

 

[trip.turlington]

Awesome! I'll post back after I get a chance to give it a try (a couple of other issues jumped in line before I can get back to this) and let you know how that works out!

Thank you both very much for your time, help, information, and options. It is very much appreciated!