Import text file into excel with preset file layout, delimeters 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.

 

[Tom Ogilvy]

OpenText method

Easiest is to turn on the macro recorder (tools=>Macro=>record a new macro)
and go through the text import wizard (file=>Open a text file and it starts
automatically if it isn't a .CSV file) to properly parse the file.

then you can turn off the macro recorder and generalize the code if
necessary

 

[Walt Weber]

Hi meldrape,

When you wrote "d:\textdump", did you mean to imply the
files arrive one at a time on CD's? You say "zillion
text files" (I'll take that as many) and "They all have
the same name "textdump"" - if so, they'll all have to be
in different directories if located on one drive. You'll
need something like the GetOpenFilename Method (See the
VBA help system for elaboration) to facilitate the user's
finding these files for opening one at a time. If
there's a way to have these files generated with discrete
names, even a counter number built into the name, you
could load them all into one specific source data
directory for processing. Then a macro could process all
files in that directory with no user intervention required.

In using "GCUST1", you seem to indicate that the source of
the files dictates which layout to anticipate on opening.

Without seeing your data or having a better description of
the relationships, it's difficult to advise further.

Best Regards,
Walt

 

[Mel Draper]

Many thanks for your ideas. I did the macro record thing and it's
great. Only thing is, it's in the particular spreadsheet I'm working
in. How can I make the macros available in Excel as a general option,
not just spreadsheet specific? Is there something available like the
normal.dot is in Word?

Thank you.
Mel

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Mel Draper]

Thanks Tom, this works great and I can modify it for each table I'm
importing. BUT how do I make the macros live somewhere besides in the
current spreadsheet? Is there some type of option available like the
Normal.dot in Word where I can store all of the macros and it loads up
each time Excel loads?

Thank you VERY much.
Mel

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

If you record a macro an designate it to be stored in your personal
workbook, then personal.xls is created and placed in the xlStart directory.
Each time you start excel, any files in the xlstart directory will be
opened, including the personal.xls. By default, personal.xls will be hidden
so it won't appear in the main excel window (although you can see it under
Window=>Unhide

You can also see it in the VBE in the Project Window.

You can copy you code to this personal.xls

Then you can access it through Tools=>Macros=>Macro

Since you recorded it, it should be designed to work on the activeworkbook.

When you close excel, it should prompt you to save changes made to
personal.xls, so the next time you open excel, the macro should still be
available to you.

 

[Tom Ogilvy]

Answered, but maybe you can't see it:

If you record a macro an designate it to be stored in your personal
workbook, then personal.xls is created and placed in the xlStart directory.
Each time you start excel, any files in the xlstart directory will be
opened, including the personal.xls. By default, personal.xls will be hidden
so it won't appear in the main excel window (although you can see it under
Window=>Unhide

You can also see it in the VBE in the Project Window.

You can copy you code to this personal.xls

Then you can access it through Tools=>Macros=>Macro

Since you recorded it, it should be designed to work on the activeworkbook.

When you close excel, it should prompt you to save changes made to
personal.xls, so the next time you open excel, the macro should still be
available to you.

 

[Mel Draper]

Tom, that is AWESOME. Many thanks for that tip. Works perfectly. I'll
be able to do everything I want to do now.

Thank you.
Mel

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!