Auto Text-to-Columns "Feature"

[doicomehereoften1]

With the latest update for Excel loaded (it just got installed today), when opening a .txt file by selecting it and choosing Open With>Excel, it is now automatically putting it the data into columns which really screws up all the macros that format the data into usable reports. I found a sort-of fix by having Excel already open, going to FILE>OPEN, then choosing the .txt file and holding down SHIFT while clicking OPEN but I the way I've taught the other staff to run these macros is to use the Open With method since it's easier.

Does anybody know how to turn this "Feature" off? I haven't been able to find anything else about it thus far.

 

[doicomehereoften1]

Update to this, I'm testing out all of my Macros and it's turning out that all of the Macros are now stopping after the TextToColumns statement. This is starting to look really bad.

 

[doicomehereoften1]

Update2: It's kind of spotty how the Macro's are stopping after the TextToColumns Statement. Some do it, some don't, and there's no real correlation as to why. It's not just stopping the Macro in place, it's Ending the Sub in place. It doesn't even skip to the End Sub statement. So far, I've had one Macro still work out of 5+ tested that contain this statement.

 

[doicomehereoften1]

So is anybody else having this problem with the latest update, or am I special?

 

[RettaJ]

I'm having this problem as well. I opened the file with notepad, saved to my hard drive as type "all files', instead of txt, opened that file with Excel & it got me down to two columns. That was managable. Any variation of the above resulted in 10 columns on the same file. This has the potential of becoming a huge problem for me due to the number of formats used on text reports I have to convert to Excel.
Help!

 

[doicomehereoften1]

You should try using the Shift+Open method that I described earlier.

Also, if you're using VBA Macros to format your .txt files, I found a solution this morning via trial and error.

Enter the code below near the beginning of all of your macros. It will change how you have to open the files so that you have to have a session of Excel already open, then run the Macro, and an OPEN dialog box will come up and you can then select the file. After this bit of code, you can have your TextToColumns code so that everything goes into the correct columns. You should also put a "Pickup:" line of code in Col 1 right before End Sub so that the Goto line will end the Sub if the user clicks Cancel.

'Open the file
fileToOpen = Application _
    .GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
    MsgBox "Open " & fileToOpen
    Else: Goto Pickup
End If
Workbooks.Open (fileToOpen)