1

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.

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.

0

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+DE)
  • 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.

  • 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 Stack Exchange; 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 Super User 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 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 Stack Exchange 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 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 Jan 21 '18 at 19:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.