how do I paste or import without parsing??

[meltemi]

I think I'm using Excel 2007, but I'm not sure because they hid the "Help
About" menu. There's a question mark icon at the upper right that leads to
online help, but the online help regarding how to find the version number
refers to menu items that I don't see anywhere.

How do I turn off automatic parsing for pastes or file imports? It always
does the wrong thing and parses on tabs. As suggested all over the web,
before the paste or import I tried doing a "Text to Columns" with settings
that will would correctly for the data I'm trying to import, but the import
still gets mangled.

 

[Dave Peterson]

Is the parsing only happening after you've run data|text to columns (or opening
a text file) in that excel session (it doesn't occur right after you open excel
and your workbook)???

I'm guessing that your earlier text to columns is causing the trouble.

Put something in an empty cell (like asdf)
then do the text to columns.
Choose delimited
but uncheck all the options
and finish up
Then clean up the cell.

Then try pasting your data.

 

[meltemi]

The parsing occurs when I open up a new worksheet and then paste from another
application. Or when I drag a text file onto an open instance of Excel. The
"text to columns" operation I was refering to was an attempt to set the
options I wanted before the paste/import. This was the suggested fix from a
number of sites. I entered garbage data into a cell and performed a
delimited "text to columns" using a char not in the data as the only selected
delimiter.

Is there a way to just turn off the automatic parsing??

 

[Dave Peterson]

Do this "dummy" text to columns before you try pasting (and don't even check the
Other character).

then try pasting your data.

If you do a text to columns against a new worksheet in a new excel instance,
you'll see that Tab is already chosen.

The only way I know to stop that is to run that dummy text to columns--either
manually or via code:

You could create a workbook and plop it into your XLStart folder that does the
work for you. Then closes to get out of your way.

This would be the code in that workbook:

Option Explicit
Sub auto_open()

Dim myCell As Range

Set myCell = ThisWorkbook.Worksheets(1).Range("A1")
With myCell
.Value = "asdf"
.Cells.TextToColumns Destination:=.Cells(1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1)
End With

'close this file
'thisworkbook.Close savechanges:=false
End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)