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,100

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
  • 229
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