Link to home
Start Free TrialLog in
Avatar of bsharath
bsharathFlag for India

asked on

Excel macro to get all txt files in UNC to a sheet.

Hi,

Excel macro to get all txt files in UNC to a sheet.
I have txt files like

Office 2007.txt
Project 2003.txt
When macro run has to get each txt file name to a column and the data within it below it.
Say i have 5000 txt file then it has to go to 5000 colums.

regards
Sharath

Avatar of kuanthye
kuanthye

...
Ho Sharath,

Is the format of each text file the same as the others or are they all different?

Patrick
Avatar of bsharath

ASKER

Hi Patrick
the format is same one line data its like a word per line
as
so-ui-edr09

something as above
Avatar of Norie
Sharath

5000 columns?

Don't think that will fit on a worksheet in a pre-Excel 2007 workbook.

Excel 2007 and onwards it'll take up almost a third of the columns.

Anyway, I think we need more information.

You say one line of data is so-ui-edr09?

Do you want to separate that using the - as a delimiter? ie 'so' in 1st column, 'ui' in 2nd and edr09 in 3rd.

If that is the case why not start with the macro recorder?

When you goto File>Open... and choose a text file Excel usually fires up the Text Import Wizard.

You can use that to set the delimiter and also specify the destination.

The code should be pretty straightforward and I don't think using UNC paths will be a problem.

One thing though is how will you know that files to import?

Do you have a list? Are they all in the same directory?
I have all txt files in a folder.
I have excel 2007
When run get the file name in row 1 and then the data within it in the same colum

Say file name is "Office 2007.txt"
then the header would be "Office 2007"
all data in the txt file below the "office 2007"
the same has to be the case with all 5000 files A,B,C,D, and so on
Ah, so it's 5000 files not columns then?

Did you try what I suggested? ie the macro recorder.

If I was doing anything like this that's where I would start.
5000 files into 5000 colums is what i need
I will need each file name and data into each colum so that makes 5000 colums
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot.... Worked perfect
Thanks Patrick for taking over this question - Patrick