VBA - "text to columns, delimitator = comma"

[MetLife]

Hi,

I'm recording a macro to do a text to columns command automatically. When I do this in record macro mode with a txt file I get the error message saying... "Too many line continuations".

Is there a way to get this to work? What code would i use?

Thanks,

 

[6StringJazzer]

I am not clear on your requirement. If you open a comma-delimited file in Excel it automatically divides the text up into columns based on where the commas are. So I'm not clear on the scenario that you need a VBA solution for.

This code will take text in column A and split it into columns based on a comma delimiter (not delimitator). I don't know what you were trying to record.

    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

 

[MetLife]

I am not clear on your requirement. If you open a comma-delimited file in Excel it automatically divides the text up into columns based on where the commas are. So I'm not clear on the scenario that you need a VBA solution for.

This code will take text in column A and split it into columns based on a comma delimiter (not delimitator). I don't know what you were trying to record.

    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True

Sorry, it was a ".txt" file. Not csv.

 

[6StringJazzer]

I know you said the extension is .txt, but if it's comma-delimited you can change it to .csv.

 

[MetLife]

I know you said the extension is .txt, but if it's comma-delimited you can change it to .csv.

I would have to make a .csv copy of the file though. I am not allowed to change the file

 

[6StringJazzer]

I created a comma-delimited file with a .txt extension, and when I opened it, Excel automatically converted the data into columns. I did not even get any prompts, it just opened like this.

4.12,0.03,4.62,5.53,1,
,,,,,
375,,,,,1545
,,145,,,669.9
,,,,99,99

 

[MetLife]

I created a comma-delimited file with a .txt extension, and when I opened it, Excel automatically converted the data into columns. I did not even get any prompts, it just opened like this.

4.12,0.03,4.62,5.53,1,
,,,,,
375,,,,,1545
,,145,,,669.9
,,,,99,99

View attachment 117598

open it up using the record macro feature. Go to File->open
you will be prompted to choose the type of delimiter before the file is loaded into excel.

 

[6StringJazzer]

I don't see what you want to do. If you want to open this file, you don't need to use a macro. If you want to convert it after it's opened but data in one column, I provided code for that.

What do you need to do that is different than my suggestions so far? Do you want to open it automatically parsing it by commas, without having to see the delimiter dialog box? You would need additional code to prompt for the filename, then open it using code like what the macro generator did.