VBA TextToColumns from hidden workbook

[elmaco12]

Hi all,

I have a hidden workbook called PERSONAL.XLSB located in C:\Users\xxxxxx\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB, that way I can store macros in that workbook that will then be available for all other workbooks I open.

I work alot with an ERP system where the export comes as text in the clipboard, separated by "|" (alt gr + <).

What I do today is paste the export in excel and the use TextToColumns, choose | as the deliminator and then finish. I am trying to find a way to use a macro to do this, and assign the shortcut Ctrl+D to it.

When I record the TextToColumns feature I get the below Macro:

Sub TextToColumns()'
' Macro1 Macro
'


'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Range("A1").Select
End Sub

That works fine as long as the macro is stored in the same workbook as it gets called on from. However, when i move the macro to my PERSONAL.XLSB workbook it stops working. Instead of converting text to columns it instead just overwrites all cells with whatever is in cell A1.

Do any one of you know how to amend the macro so it works in whatever workbook it is called in?

Thank you very much for your support. Really appreciate it .

 

[Trevor G]

Welcome to the forum, what if you try to add this as the first line of your code

ActiveWorkBook.Selection.TextToColumns

 

[Trevor G]

This reply went through twice....

 

[elmaco12]

This reply went through twice....

Hi Trevor,

thanks for your reply. I tried that now but it still just copies the text in A1 down the way until the end of the selected cells... (in this case A1:A587).

Code used now is:

Sub TextToColumns()' Macro1 Macro
'
'
        ActiveWorkbook.Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Range("A1").Select
End Sub

 

[elmaco12]

Bump...

 

[Trevor G]

If ActiveWorkbook isn't working try using ThisWorkbook