Opening a text file using a macro 2

[TampaBridgeDesign]

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!

 

[TomBarsh]

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

 

[GregLocock]

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.

 

[TampaBridgeDesign]

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.

 

[ab123456]

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.

 

[johnwm]

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

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:

http://www.essexsteam.co.uk

 

[griffengm]

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

Griffy

 

[TampaBridgeDesign]

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!

 

[prost]

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

 

[TampaBridgeDesign]

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




Thanks to everyone who responded to my request!!

 

[prost]

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.