Results 1 to 6 of 6

Auto Text-to-Columns "Feature"

This is a discussion on Auto Text-to-Columns "Feature" within the Excel Questions forums, part of the Question Forums category; With the latest update for Excel loaded (it just got installed today), when opening a .txt file by selecting it ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Location
    Greeley, CO
    Posts
    31

    Default Auto Text-to-Columns "Feature"

    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.

  2. #2
    New Member
    Join Date
    Feb 2008
    Location
    Greeley, CO
    Posts
    31

    Default Re: Auto Text-to-Columns "Feature"

    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.

  3. #3
    New Member
    Join Date
    Feb 2008
    Location
    Greeley, CO
    Posts
    31

    Default Re: Auto Text-to-Columns "Feature"

    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.

  4. #4
    New Member
    Join Date
    Feb 2008
    Location
    Greeley, CO
    Posts
    31

    Default Re: Auto Text-to-Columns "Feature"

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

  5. #5
    New Member
    Join Date
    Feb 2008
    Location
    California
    Posts
    9

    Default Re: Auto Text-to-Columns "Feature"

    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!

  6. #6
    New Member
    Join Date
    Feb 2008
    Location
    Greeley, CO
    Posts
    31

    Default Re: Auto Text-to-Columns "Feature"

    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.

    Code:
    '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)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com