file name macro

  • Thread starter Thread starter Bryan Kelly
  • Start date Start date
B

Bryan Kelly

I have been working on a macro to import a text file and make some changes
to prepare it for analysis. I have most of the changes working (pending
another question in this forum). I now need a method for the user to be able
to enter the file name to open. Preferable, they should be able to open some
type of browser window and select the file they want. Lacking that, at least
be able to type in the file name.

The book I have discusses building forms, but does not say how to get a form
into a macro and how to access the value of a variable. The first line of my
macro looks something like this:

Workbooks.OpenText FileName:= "C:\WORK\test.txt", Origin:= _

<snip>

I want to change this to something like:

<activate function to get file name>

filename = <what_ever_the_function_returned>

Workbooks.OpenText FileName:= _

"<value of filename>", Origin:= _

<snip>

How should this be done?
 
How about using a macro to create the list of file names on another sheet
somewhere.
Then that list is a defined name range(self adjusting for additions and
deletions) that is tied to a drop down list from which the user can select.
 
Hello Don,
Thanks for your reply. That sounds like a good idea. Can you tell me how?
To keep this simple, I need to analyze a bunch of data but I simply don't
have time to become an Excel and/or VBA expert. I need some help.

I have a macro to prepare my data for analysis. The flaw is that I must
either change the names of all my files (one at a time and then back to the
original) to match my macro or edit my macro for each different file.
Neither is appealing. I need a macro to provide the user, me, the ability to
select a file for analysis via a simple form. Preferably some type of
browsing tool just like so many programs currently use. For example, Excel
itself when I engage the Open option.

Does anyone have a macro that will perform this task?

Thank you,
Bryan
 
Bryan,

You can use the Application.GetOpenFilename method to do this.
For example,

Dim FName As Variant
FName = Application.GetOpenFilename("Excel file (*.xls),*.xls)")
If FName = False Then
MsgBox "No file selected"
Else
MsgBox "You choose: " & FName
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip,
That is exactly what I need. But now I need to show my ignorance with a
followup question. When I pasted these commands in the front of my macro,
it brought up the file browser. However, I need to import a text file. I
changed the second line to read:

FName = Application.GetOpenFilename("Excel file (*.txt),*.txt)")

When run again, I can now see the text file and select one. After that, the
messge box contained test.txt, exactly what I wanted. Now I had to use it
in my macro.

The code was:

Workbooks.OpenText FileName:=

"C:\BMRST WORK\JDMTA\B string\ERROR GRADIENT\test\test.txt", Origin:= _ <
etc, etc>

I changed the file name part to be

Workbooks.OpenText FileName:= &FName , Origin:= <etc,etc?

and to

& FName, and to "&FName" and "& FName" and in all cases I get a debug error.
Tell me what this should look like to use the value of the variable FName in
my macro. Here is what the macro recorder wrote when I did a record
session.

Workbooks.OpenText FileName:= _

"C:\BMRST WORK\JDMTA\B string\ERROR GRADIENT\test\test.txt", Origin:= _

xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _

xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _

And if you can, tell me how to make this stinking Outlook Express stop
putting all the blank lines in there when I cut and paste from another file.
I selected Format | Paragraph and it just ignored the paragraph selection.
It just won't format a paragraph.

Thanks again for your help,
Bryan
 
Bryan,

Get rid of the & character completely.

Workbooks.OpenText Filename:=FName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Great, that caps it off. Just exactly what I needed.
Thank you for your help,
Bryan
 
Back
Top