manipulating multiple csv files.

[avishain]

Hello all,

i recieve every month about 20-25 csv files which located in the same folder (say under c:\my files)

these files are are completly structered the same (columns a to t),however,the length of each file may vary ( 1 file can contain 100 rows and the other 2000 rows).

the name of each file is always in this structure :

standart_201103_0001_00000064_00001111_03241234.CSV

another 1:

standart_201103_0001_00000064_00003232_03241554.CSV

in each csv file there is only 1 worksheet.

what i need is the following:

1.i want that each CSV will change its worksheet name to the number which appears in the 35 char from the left of its name.

for instance:

standart_201103_0001_00000064_00001111_03241234.CSV

so the worksheet name would be 1111

standart_201103_0001_00000064_00003232_03241554.CSV

so the worksheet name wouuld be 3232

2.then,i want that all worksheets (within each csv file) will be copied to 1 main file (XLSX file) .

3.then,when i have this main workbook i want to open a new worksheet (inside this main workbook) and i want all the data from all worksheets ("1111","3232" and so on..) will be copied to this worksheet,so eventually ill have 1 table consist all of the data that i had and these csv files. ( i remind you again,all the csv files have the same structure,coulmns A to T)

thank you very much!1

 

[Andrew Poulsom]

This should get you started:

http://www.rondebruin.nl/csv.htm

 

[avishain]

This should get you started:

http://www.rondebruin.nl/csv.htm

thank u very much

what about changing the worksheets names according to their filenames?

 

[Andrew Poulsom]

thank u very much

what about changing the worksheets names according to their filenames?

What have you tried? The code here names each sheet:

http://www.rondebruin.nl/txtcsv.htm

Try:

mysheet.Name = Mid(Right(TxtFileNames(Fnum), Len(TxtFileNames(Fnum)) - _
                                    InStrRev(TxtFileNames(Fnum), "\", , 1)), 35, 4)