How do I disable auto text to columns with VBA?

[Ago]

I have a excel add in that runs every time I open a file (sheet activate) and when I open csv or srt file the macro keeps going and if not csv/srt it ends.

Now the problem is Excel 2010 and newer has auto text to columns and it destroys file.
Is there any way you can disable it if the file is csv or srt by VBA code?

I don't want the user to have make settings for it to work.

[Pasi12]

Ago,

Here... this might work you:
If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).


Sub ClearTextToColumns()
On Error Resume Next
If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=""
If Range("A1") = "XYZZY" Then Range("A1") = ""
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

[Ago]

Ago,

Here... this might work you:
If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).


Sub ClearTextToColumns()
On Error Resume Next
If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
Range("A1").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=""
If Range("A1") = "XYZZY" Then Range("A1") = ""
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.


I see what the code does but I'm not sure it will fit my problem.

The user rightclicks on a SRT file and then open with MS Excel. (Or makes Excel standard software and just opens it)
At this point the sheet is already filled with the data, and in 2007 everything is in column A but apparently in 2010/2013 it does auto text to columns.
I think I managed to create a very dirty workaround, but because I don't have 2010/2013 I havn't been able to test it.



wkbName = Application.Workbooks(1).Name
FileName = Application.ActiveWorkbook.Path & "\" & wkbName

If Range("B2").Value <> "" Then 'Text to columns has been done
Cells.Delete Shift:=xlUp
FileNum = FreeFile()
Open FileName For Input As #FileNum
i = 1
While Not EOF(FileNum)
Line Input #FileNum, DataLine
Range("A" & i).Value = DataLine
i = i + 1
Wend
End If


But what I'm looking for is a "When Excel starts" function and a way to disable the text to columns.
But I guess there is no such settings