Parsing data macro
 

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...
--
C.J. Miller

Parsing data macro
 

On Wed, 25 Jan 2006 18:31:02 -0800, "cjmillerFL"
wrote:

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

Parsing data macro
 

Thanks Ron,

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

--
C.J. Miller


"Ron Rosenfeld" wrote:

On Wed, 25 Jan 2006 18:31:02 -0800, "cjmillerFL"
wrote:

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

Parsing data macro
 

On Wed, 25 Jan 2006 19:20:02 -0800, "cjmillerFL"
wrote:

Thanks Ron,

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

--
C.J. Miller

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

Parsing data macro
 

On Wed, 25 Jan 2006 19:20:02 -0800, "cjmillerFL"
wrote:

Thanks Ron,

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

--
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)
-------------------------
--ron

Parsing data macro
 

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


"Ron Rosenfeld" wrote:

On Wed, 25 Jan 2006 19:20:02 -0800, "cjmillerFL"
wrote:

Thanks Ron,

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

--
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)
-------------------------
--ron