Make the procedure of import of a text file in Excel automatical

[sleepingSatellite]

I have nearly 60 text files.
Every file is made of a series of rows of data. Each piece of data separated by semicolumn.

In principle I should open each of them (from Excel) as a text file, use the Text Assistant to recognize the columns and save in Excel-table format, in order to have the data available to be handled with Excel. Then repeat the procedure for every file.
Has anybody made a macro to do it automatically ?

 

[PHV]

Play with the macro recorder to get started.
Then take a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sleepingSatellite]

I've recorded the procedure to open a text file and save it as an Excel file. There's no problem when I record, but when I try to execute the recorded macro, it tells me that there's "No space enough, error 7" (I translate, my Excel is not in english) and puts in evidence the part I've reported in bold.
I exclude having exhausted the resources of my PC.

Sub Makro1()
'
' Makro1 Makro
' Makro am 28.01.2005 von sleepsat aufgezeichnet
'
' Tastenkombination: Strg+a
'
    ChDir "D:\support"
    Workbooks.OpenText FileName:="D:\support\data1.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
        Comma:=False, 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), [b]Array[/b](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))
    ActiveWorkbook.SaveAs FileName:="D:\support\data1.xls", FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
End Sub

 

[PHV]

What is in bold ?
Does "D:\support\data1.xls" already exists ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

So you have a colon delimited text file which maps to 54 columns.

How many rows?

You do realize that Excel has a 65,536 row limit.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[sleepingSatellite]

The word "Array" corrisponding to the 40th element is in bold.
data1.xls doesn't exist yet.
The Help suggests, among other, less realistic causes,
1) A module or a procedure could be too large
2) There's no more space available for public variables

If I do it by hand it works without any problem.

 

[sleepingSatellite]

Only 50 rows, and effectively there's never been any problem doing it by hand.

 

[sleepingSatellite]

It's a compilation error. A compilation error in the software Excel has itself created.

 

[SkipVought]

I'd look at the data after the 39[sup]th[/sup] colon in each row. Is there something in that data that might be weird? Look in the SOURCE DATA.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]