I'm trying to find a setting that controls what Excel uses for delimiters in PasteSpecial, Import and Transpose. I'm running Excel 2011 under Mac OS X High Sierra.

Here's my problem. I have a routine that pastes (using PasteSpecial) the clipboard into a worksheet. The clipboard has been copied from a text file that has two columns separated by a Tab character. It has been working correctly for months, placing the text in two worksheet columns. The code looks like:

    ' copy clipboard text to files worksheet
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Suddenly it started dropping the text into four columns, and of course all the subsequent routines that use the data failed.

It turns out, the failures started after I used a Data > Text-to-Columns control on the ribbon to split something else, based on a hyphen delimiter. When I went back to pasting the clipboard, it was using the hyphen delimiter. The PasteSpecial has no way to change it, since it doesn't ask for a delimiter, even though Text-to-Columns and Import do.

Side Note: the reason I'm using PasteSpecial from the clipboard instead of Import is that Excel 2011 does not allow the user or the developer to clear out the Data Connection created during an import. (Plus, the paste special appears to be faster.) This causes a warning message each time the workbook is opened - very annoying, and not something I want my users to experience.

It appears that Text-to-Columns sets the very delimiter that Import and PasteSpecial also use. Is there anyplace other than using Text-to-Columns to control that delimiter?

I have a workaround: run VBA's Range.TextToColumns method before each PasteSpecial to guarantee that my delimiter is exactly what I want.

   ' in a clean worksheet, transpose a single cell
    Range("A1").Select
    Selection.FormulaR1C1 = "X"
    Selection.TextToColumns _
        Destination:=Selection, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 1)
This turns on the Tab but turns off the others. As I said, it's a workaround and I consider it clunky and kludgey, but it does the job I need.

Is there a cleaner way to do this? Some basic delimiter setting that these methods use?