Macro to Convert CSV to excel

[samfolds]

Hi,

I've been trying to write a macro that would automatically select the text in CSV and transform it into excel format. Normally this is easy (record macro), but I need to do that for a large spreadsheet and an error message occurs when I try to record the macro (Continuity chain too long)...

Here's the code :

 Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited,  _
        TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE,  _
        Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, 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),Array(56,1),Array(57,1),Array(58,1),Array( _
        59,1),Array(60,1),Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1), _
        Array(66,1),Array(67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array( _
        72,1),Array(73,1),Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1), _
        Array(79,1),Array(80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array( _
        85,1),Array(86,1),Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1), _
        Array(92,1),Array(93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array( _
        98,1),Array(99,1),Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104 _
        ,1),Array(105,1),Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110, _
        1),Array(111,1),Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1 _
        ),Array(117,1),Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1) _
        ,Array(123,1),Array(124,1),Array(125,1),Array(126,1),Array(127,1),Array(128,1), _
        Array(129,1),Array(130,1),Array(131,1),Array(132,1),Array(133,1),Array(134,1), _

But then it stops and says it can't continue. I have 11500 lines in Excel 2003.

Thanks in advance!

Samfolds

 

[Jon von der Heyden]

Hi Samfolds

Do you want to import all the data from the CSV? If you open the CSV file directly into excel do all of the fields/columns appear correctly formatted (i.e. correct data types, dates/values etc)?

If so perhaps you could rather code it to open the CSV, then copy the entire data range, and paste back into the sheet.

 

[samfolds]

Perhaps I wasn't clear enough... Let me rephrase my problem :

I'm willing to copy/paste the data from text to excel, but then I absolutely need to transform the text into columns (using the converter -> comma delimited).

The problem is : There is just too much data for vba macro recorder to be happy. So my question is basically : is there a better way to code what I'm trying to do, one that wouldn't exceed vba's capacity?

To answer your question, YES, I want to get all the data from the text file and when I convert csv to fit excel, then YES everything works fine and all the data I need is right where it should be.

Thanks a million!

 

[Jon von der Heyden]

Hi again

I understood the question. But I don't necessarily think that the steps that you are recording is necessary. I am assuming that your text file is in csv format, so therefore perhaps quite simply:

[COLOR="Blue"]Dim[/COLOR] wkbText [COLOR="Blue"]As[/COLOR] Workbook
[COLOR="Blue"]Set[/COLOR] wkbText = Workbooks.Open(Filename:="C:\Excel\Files\Text\Test1.csv")
wkbText.Sheets(1).UsedRange.Copy
ThisWorkbook.Sheets(1).Range("A1").PasteSpecial
Application.CutCopyMode = [COLOR="Blue"]False[/COLOR]
wkbText.Close savechanges:=[COLOR="Blue"]False[/COLOR]

 

[AlphaFrog]

I agree with Jon von der Heyden in that there's probably a better way to do this. Having said that. Here's your macro code modified so that it "should" overcome the "Continuity chain too long" limit in VBA.

    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False

You don't need the "FieldInfo:=Array( ..." part of your recorded macro unless you want to specify different formatting for each column e.g. you want column C to be a date and column F a number etc.

Eliminating the "FieldInfo:=Array( ..." part of the code means all the columns will be formatted as General.

 

[samfolds]

Thanks to you both!

Jon, my file was not in csv format but in txt format. I believe it's quite easy to change it, but the other problem would be that the file used this particular time is not always the same (therefore the path is not the same).

AlphaFrog : Your macro worked like a charm . It's nice to be able to record macros, but in this particular case human knowledge triumphed :P

Thanks again!