How to automate Text to Column (VBA)

[j4ttlife]

Hi All,

I've been trying to convert a large dataset from Text to Columns using VBA.

The data starts in column A is similar to the format below:

name~address~city~occupation~salary

john~10 downing street~london~dentist~90000

My actual data will have around 28 columns, and will have over 50,000 rows (though this can differ daily).

I wanted to make it dynamic as the number of rows I get each day may differ but wasn't sure how, so instead I just used the code:

Range("A1:A1048576").TextToColumns

This code worked yesterday but now my macro is skipping over the line of code without executing it. There is no error.

Is there a simpler way to dynamically convert an entire spreadsheet from Text to Column that's in the above formatted as the data below?

Thanks.

 

[Joe4]

You shouldn't need to designate rows. You should be able to just do something like:

    Columns("A:A").TextToColumns ...

 

[j4ttlife]

You shouldn't need to designate rows. You should be able to just do something like:

    Columns("A:A").TextToColumns ...

Thanks, I've made the changes you suggested but it's not working. Again there's no error.

I get a minibar towards the bottom right that momentarily loads called 'Parse' but after this fully loads nothing changes and my macro skips to the next line of code.

 

[Joe4]

Please post your code in its entirety, so we can see all the options and settings you chose for the Text to Columns functionality.

 

[j4ttlife]

My code is work related so don't think I should be posting the entirety of it here, but I've posted as much as I can (it's a fairly small module so this the bulk of it).

• I have a button in a Workbook 1 that opens a new document (Workbook 2)
• Workbook 2 is a single sheet workbook in the same format as the image above
• I convert this text to columns
• I copy the sheet in its entirety and paste onto Workbook 1.

Dim newWB as Workbook, fd as FileDialog


Set fd = Application.FileDialog(msoFileDialogOpen)



With fd

.Filters.Clear

.Title = "Choose relevant file"

.AllowMultiSelect = False

.Show

.InitialFileName = "\\xxxxxx"

.Execute

End With





Set newWB = Workbooks(2)





newWB.activate

Range("A:A").TextToColumns

ActiveSheet.Cells.Copy

ThisWorkbook.Activate

Sheets.Add after:=ActiveSheet

ActiveSheet.Paste

 

[Joe4]

You are missing a ton of information for your TextToColumns function in VBA. You need to tell is what delimiter to use, etc.

If you turn on your Macro Recorder, and record yourself applying TextToColumns, and then stop the recorder.

You will immediately know if you have done it correctly (you will see the fields split up).
If you have, then view your recorded code, and copy the rest of the "TextToColumns" function arguments to the code that you have now.

The Macro Recorder is a great tool for getting snippets of code like this.

 

[j4ttlife]

You are missing a ton of information for your TextToColumns function in VBA. You need to tell is what delimiter to use, etc.

If you turn on your Macro Recorder, and record yourself applying TextToColumns, and then stop the recorder.

You will immediately know if you have done it correctly (you will see the fields split up).
If you have, then view your recorded code, and copy the rest of the "TextToColumns" function arguments to the code that you have now.

The Macro Recorder is a great tool for getting snippets of code like this.

Thank you, I will try that way. Will get back to you, hopefully with it working!

 

[Joe4]

OK. Post back if you run into issues.

Note that "TextToColumns" has parameters that need to be set (whether it it Fixed Width or Delimiter, the Delimiter, and Field Types for each field).
I believe if you leave all those off, it may just default to whatever was done last.
And you really DON'T want to leave that to chance.

 

[j4ttlife]

OK. Post back if you run into issues.

Note that "TextToColumns" has parameters that need to be set (whether it it Fixed Width or Delimiter, the Delimiter, and Field Types for each field).
I believe if you leave all those off, it may just default to whatever was done last.
And you really DON'T want to leave that to chance.

Hi, I managed to get it working using the macro recorder, but I mentioned in the first post that I might have a different number of columns in each dataset I receive.

My code is as below:

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="~", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
        51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1)), TrailingMinusNumbers _
        :=True

I'm guessing the Arrays represent the number of columns (I have 55 columns this week), but the next week I may have 53 or 56.

 

[Joe4]

Hi, I managed to get it working using the macro recorder, but I mentioned in the first post that I might have a different number of columns in each dataset I receive.

My code is as below:

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="~", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
        51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1)), TrailingMinusNumbers _
        :=True

I'm guessing the Arrays represent the number of columns (I have 55 columns this week), but the next week I may have 53 or 56.

Try creating it for the maximum number of columns that you may have (maybe 60?), and see if that works.