2

When I'm using Excel to parse multiple documents, and I need to control the import (Text, not General, on column import settings being the most notable example), how can I prevent Excel from automatically running the text-to-column import wizard for me automatically. I prefer to run it manually everytime, even tho I realize that Excel is just trying to be helpful by doing the thing I just did.

It just doesn't convert the columns to Text first.

jcolebrand
  • 1,090

2 Answers2

1

If you are on a version of Excel that does not contain "Get External Data" under Data menu (such as Excel 2003, which is my primary version due to the anti-productive "upgrades" of Office 2007 through 2013), you can prevent the "helpful" behavior they force on you either by a few manual steps or by a macro (a macro is faster & easier, so I put it in my personal.xls):

  • Add a workbook (or Ctrl+N)
  • Type the letter A in cell A1
  • Click cell A1
  • Start “Text to Columns” (or Alt+D, E)
  • Choose delimited (or Alt+D)
  • Hit Enter
  • Unselect every checkbox
  • Click Finish (or Alt+F)
  • Close the workbook; do not save.

Now the "helpful" behavior will be prevented when you paste again.

In code,

' Differs from Walkenbach in that he just populated A1 if empty, did this on A1, restored A1.
' This is better IFF you are allowed to add a workbook.
Sub FixAutomaticTextToColumns()
    If ActiveSheet.ProtectContents Then MsgBox _
            "BTW, the active sheet is protected. Consider unprotecting 'ere TextToColumns"
    Workbooks.Add
    Cells(1) = "A" 'Required, else TextToColumns will error
    Cells(1).TextToColumns DataType:=xlDelimited, Tab:=False, semicolon:=False, _
                           comma:=False, Space:=False, other:=False
    ActiveWorkbook.Close False
End Sub

FWIW, I wrote this routine independently, and then discovered that Excel guru / author / developer John Walkenbach had published something similar on his site, The Spreadsheet Page, titled Clearing The Text To Columns Parameters.

See How do I add VBA in MS Office? for general information.

Community
  • 1
MicrosoftShouldBeKickedInNuts
  • 219
  • 1
    At any rate, thank you for the formatting assistance, though I'm just going to delete this since my excellent work here is apparently highly unappreciated.

    I spent some time to devise, test, and make concise a useful post. And anyone who is not just trolling to downvote would see that this IS a useful, high quality post. What is it with you downvoters? You're just cutting out valuable posts for those who actually WANT answers.

     – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 15:18
  • 1
    There's no plagiarism. I made an informative comment that people who understand Excel may find beneficial; if not, just ignore it. In VBA a line preceded with an apostrophe is non executable code so you will suffer no damage by ignoring. Meanwhile, you can Google Walkenbach if you're an Excel user that is doesn't know his name; he is one of the most famous Excel gurus.

    Since I suspect you're dying to know, I wrote a routine on my own. Then I googled other solutions on the net in order to avoid wasting readers' time. I found Walkenbach's solution, but it had tradeoffs; ergo, my comment.

     – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 15:23
  • Plagiarism and spam are big problems on [SE]; so much so that we sometimes ask people to bend over backwards to preemptively deny it to avoid the appearance of impropriety.  … … … … … … I know a bit about Excel; I have written 44 answers about it, with an average of almost 1.5 upvotes each.  But I’m self-taught (with help from [SU] and other good websites), so I’m not familiar with Walkenbach’s books, articles, software, etc.  And while I doubt that there’s anybody exactly like me, I suspect that there are people like me to the extent that they know Excel and don’t know Walkenbach,  … (Cont’d) – G-Man Says 'Reinstate Monica' Jan 21 '18 at 19:13
  • (Cont’d) …  so just dropping his name in a comment (and, yes, I know that a line preceded by an apostrophe in VBA is a comment) raises more questions than it answers.  And we like posts on [SE] to be self-contained; remarks like “you can Google __________ if you don’t know” are not well received.  … … … … … … … … … … … … … … … … … … …… … … … TL;DR I’ve edited your answer again to be clearer, more complete, and more educational (without being snarky).  You are of course free to rollback and/or further revise. – G-Man Says 'Reinstate Monica' Jan 21 '18 at 19:13
  • FWIW, I didn’t downvote you, and in fact I just upvoted.  … … … … … … … … … … … … … … … … …  P.S. You don’t say where you live.  I live in North America (hint: north of Mexico) and I would say that the US decommissioning of the analog television broadcast system was the most technologically destructive move in history.  Otherwise I agree with most of what you say about Microsoft.  But personal attacks are also discouraged here. – G-Man Says 'Reinstate Monica' Jan 21 '18 at 19:13
  • [My comments above were in response to other comment(s) which were later deleted] – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 19:55
  • Please forgive my misdirected disgust, G-Man. My reaction is based on the tragic reality that superuser has become less concerned with useful or academic exchange than with downvoting like vapid sorority "mean girls" or schoolyard bullies. I've written one of the best posts of my life here - if I added more, or subtracted, it would lose its harmony of conciseness and thoroughness and just-enough-context-to-make-it-make-perfect-sense, all with well honed, highly tweaked clarity, making it simple enough for a beginner to use, but not so simplistic that a very advanced expert would find it(cont.) – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 20:03
  • (cont.) to be tedious. (I did debate putting in the shortcuts; power users and the uberproductive love that, and it stayed in.)

    Maybe the downvotes are from precious Seattle fanboys who don't like my name. Well I hope I helped them feel special at how they helped the world today, just like their employer does (Kidding^10). Meanwhile, let's get busy, insecure bullies. You can do better than 5 downvotes in a day. Let's go for a record. Can we get 500 downvotes in one week?

     – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 20:03
  • Thank you for the helpful edits and encouragement, G-man. However I wonder why I bother, on superuser, the sniper's paradise. I get - I mean, I GET - the fierce devotion to keeping answers (and questions) HQ by attacking non-clarity, post duplication, lack of examples, lack of attribution, expression of opinion as fact, and - lest we forget - errors themselves(which seem to be the least of the concerns). That all makes sense. I hate wasting my time reading lame and lazy posts. As such Superuser has no danger of becoming Yahoo Answers. – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 20:03
  • However the obsessive attacking and Lord-Of-The-Flies downvoting is causing it to implode. I wanted to give back. Instead, I'm more inclined to block the site. – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 20:04
  • Solution works beautifully in Excel for Office365 in March 2024. Symptom is no longer a frustrating "feature". – klausnrooster Mar 14 '24 at 14:44
0

You can change the layout or properties for the imported data at any time. On the Data menu, point to Get External Data, and then click either Edit Text Import or Data Range Properties.

If you select Edit Text Import, select the file that you imported originally, and then make changes to the external data in the Text Import Wizard.

By selecting Data Range Properties, you can set query definition, refresh control, and data layout options for the external data.

Ruskes
  • 340
  • 2
  • 11