Import text file and set file layout, etc. in VBA

[meldrape]

Greetings, it's been forever since I've used vba in Excel so please bear with me, plus I really don't know how to search to see if my question has been answered already. I have a zillion text files being exported from an old unix system. They all have the same name "textdump". But they need to be imported into Excel and they each have different file layouts. Since I'm using test data right now, the day will come soon that i'll need to start importing the real data. I imported the test data manually. I have all of the file layouts. Is there a way to programmatically import these files into Excel based on a function where I can pre-set the delimeters, the field names and data types, then run a macro or whatever to find d:\textdump and import it. If the dumped file is from GCUST1 then I have a function named GCUST1, etc. Tall order probably. Would appreciate any thoughts you have on this idea. Thank you very much in advance.

 

[PHV]

Have you tried to use the macrorecorder and then customize the so produced code ?

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

 

[meldrape]

I'll try it. Then if something doesn't work I'll at least have code to post . Many thanks for your help!!

 

[meldrape]

I used the macro recorder and all is well. I can just name each of the macros the name of my table. BUT how can I store all of these macros somewhere so they are available anytime I want them? I would like them all to be together in one spreadsheet and maybe it can load like the normal.dot is Word so they are available all the time? Is there such a thing? I exported the modules I've created thus far but now what do I do? Thanks!!

 

[PHV]

Take a look at the personal.xls special workbook.

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

 

[meldrape]

It helped a lot. I looked right after I posted my question. Got everything working perfectly. Thanks again for your help!

 

[kawnz]

where do you find personal.xls? I tried to search my C:\ drive and nothing showed. I did however, create the sheet.xls and book.xls files and places them in my XLSTART directory. Should I do the same with personal.xls? i.e. create it and save it?

 

[meldrape]

Hi Kawnz,

Here's a helpful link.

http://www.personal-computer-tutor.com/personalxls.htm

I hope this clears things up a bit.