Parsing data macro

[Guest]

Is there a macro (existing or that can be written) to parse data? I have a
text file that I import into Excel. The first option I get once opening this
file is the Text Import Wizard. Currently, I manually step through each
import wizard step to parse. I would like to automate this importing/parsing
exercise since this is something that I do frequently.

Any info or ideas would be much appreciated!

Regards...

 

[Ron Rosenfeld]

Is there a macro (existing or that can be written) to parse data? I have a
text file that I import into Excel. The first option I get once opening this
file is the Text Import Wizard. Currently, I manually step through each
import wizard step to parse. I would like to automate this importing/parsing
exercise since this is something that I do frequently.

Any info or ideas would be much appreciated!

Regards...

You could record the steps you take in a Macro, and then use that.

Before you open the text file, select Tools/Macro/Record New Macro.

Then go through the import process and you'll record your macro. You may need
to "clean it up" a bit after you've done that. And you may want to save it in
personal.xls so it'll be available whenever you do subsequent imports.
--ron

 

[Guest]

Thanks Ron,

That worked very well. As a followup, is there anyway to have the macro
prompt me for the file to open?

 

[Ron Rosenfeld]

Thanks Ron,

That worked very well. As a followup, is there anyway to have the macro
prompt me for the file to open?

There is. You can even modify it to go through the File Open dialog box so you
can browse through the folders. But I don't have the code handy for that.
Perhaps someone else will chime in.


--ron

 

[Ron Rosenfeld]

Thanks Ron,

That worked very well. As a followup, is there anyway to have the macro
prompt me for the file to open?

Here's something simple I came across, but I don't have enough experience in
this area to know the potential pitfalls:


---------------------
Dim fn As Variant

fn = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fn = False Then Exit Sub

Workbooks.OpenText FileName:=fn _ ...

(the rest of your data/text to columns process)

 

[Guest]

Thanks! I'll give it a try...
--
C.J. Miller

Here's something simple I came across, but I don't have enough experience in
this area to know the potential pitfalls:


---------------------
Dim fn As Variant

fn = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fn = False Then Exit Sub

Workbooks.OpenText FileName:=fn _ ...

(the rest of your data/text to columns process)