×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Opening a text file using a macro
2

Opening a text file using a macro

Opening a text file using a macro

(OP)
I have a text file as output from a live load generator.  I will always put the file in the following directory:

C:\LoadRating\

Say I have a text file called "HS20.txt" in the C:\LoadRating\ folder.  I want to put "HS20.txt" in Excel cell A1 and then have Excel open that file.  How do I write the macro such that it opens the file that I specify in cell A1?  Is this even possible?  

Thanks!

RE: Opening a text file using a macro

try somethting like this: (where you have named as  "inputFile" the cell in which you put the file name)

Option Explicit

Sub Tampa1()
   Dim inFile  As String
   
   inFile = Range("inputFile")
   Workbooks.Open (inFile)
End Sub

You may have to tweak things so that the text file is parsed as you like it when it is opened in Excel. You might find something like the VBA "Input" or "Line Input" commands useful (reads data from text file and you can parse within the VBA code).

RE: Opening a text file using a macro

The easy way to build things like this is to record your actions as a macro, then edit it.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Opening a text file using a macro

(OP)
Greg-
I've recorded my actions as a macro many times before but I've never had a situation like this where I could have up to 100 different files that I need to open.  The goal was to be able to put the name of the file I want to open in cell A1 and then press a button and have that file open.

RE: Opening a text file using a macro

If you know the file you wish to open you can use

Workbboks.OpenText sFilename

where sFilename is the full path and file name of the path to open

If you dont know the name of the file to open put the following line of code before the Workbooks.opentext line

sFilename = Application.GetOpenFilename()

which will display to standard microsoft open file dialog and allow the user to select the file to open.

RE: Opening a text file using a macro

If you really want to type the file name in cell A1 and then click a button:

CODE

Private Sub CommandButton1_Click()
sFilename = Cells(1, 1)
Workbooks.OpenText sFilename
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Opening a text file using a macro

Is this a variant of my question in "How to include contents of a cell in a link?"?

Griffy

RE: Opening a text file using a macro

(OP)
johnwm-
That's exactly what I was looking for!  I'll just have to modify it slightly to align the columns.  Thanks for your help with this!

RE: Opening a text file using a macro

An alternative solution:
Option Explicit

Sub ReadFile()
    Dim inFile As String, Folder As String
    Folder = "LoadRating"
    inFile = Cells(1, 1).Value
    inFile = "C:\" & Folder & "\" & inFile
    Open inFile For Input As #1

.
.

 Create a button in a worksheet that is assigned to
 macro ReadFile

RE: Opening a text file using a macro

(OP)
prost-
Thanks for your response.  What you have there will give me a little more flexibility.




Thanks to everyone who responded to my request!!

RE: Opening a text file using a macro

You're welcome, glad to help. I like this approach because I can also read in the name of the Folder in say with Folder=Cells(2,1).Value, and point to any folder on my PC from any other folder.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources