Text to Columns issue in excel VBA

Question

I am trying to create a macro that when run in one workbook will open up another workbook in the background, change column F of this newly opened workbook from text to date, save changes and then close the workbook that was just opened. In the code I am trying I keep getting the error message 'Run-time error '1004': TextToColumns method of Range class failed'. Do you know what is wrong and how I can fix it?

Sub Test()

Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

xlApp.Visible = False

Set xlWB = xlApp.Workbooks.Open("directory_file_to_open")

Set xlWS = xlWB.Worksheets("worksheet_of_data")
xlWS.Unprotect

    xlWS.Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

Set xlWS = Nothing
xlApp.DisplayAlerts = False
xlWB.Close True
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub

Axel Richter · Accepted Answer · 2016-09-12 10:13:31Z

1

You need to fully qualify the Destination:=Range("F1"). If not it will be Range("F1") in ActiveSheet. This is probably wrong since it should be Range("F1") in xlWS.

So

xlWS.Columns("F:F").TextToColumns Destination:=xlWS.Range("F1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True

answered Sep 12, 2016 at 10:13

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Text to Columns issue in excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related