accessing a text file with a variable name

Duritz

Board Regular
Joined
Apr 28, 2005
Messages
226
If the text file name is not set, but will be listed in A1, what's a little macro I can write to access the textfile specified in A1?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Duritz

Board Regular
Joined
Apr 28, 2005
Messages
226
Actually not quite. The file I am accessing is a textfile, and the path & filename are contained in A1, and I just want in A2 for the info from that textfile to be displayed, know what I mean?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
know what I mean?
Yep, I think so.

As long as the entire path & file name (including the file extension) exists in A1 of the
active sheet, does something like this work for you?
Code:
Sub Demo()
Dim fName$
fName = [A1].Value
Application.ScreenUpdating = False
On Error Resume Next
If Len(fName) <> 0 Then
  Workbooks.Open fName
  If Err = 0 Then
    With Workbooks(fName)
      'change Sheet1 to the sheet with the value you want from A2
      'change A2 to the cell you want the value of
      'change D10 to the cell you want that value displayed in
      .Sheets("Sheet1").[A2].Copy [D10]
      .Close False
    End With
  Else
    MsgBox ("The file '" & fName & "' does not exist in the specified file path."), , fName & " not found"
  End If
End If
Err.Clear
Application.ScreenUpdating = True
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Hi
Perhaps
Code:
Sub test()
Dim myF As String, ff As Integer, txt As String
ff = FreeFile
myF = Range("a1").Value
txt = Space(FileLen(myF))
Open myF For Binary As #ff
   Get #ff, ,Txt
Close #ff
Sheets("Sheet1").Range("a2").Value = txt
End Sub
 

Duritz

Board Regular
Joined
Apr 28, 2005
Messages
226
Hi HalfAce, thanks that's almost done it. Only problem is instead of displaying the results of the textfile in cell A2, it displays them in cell A1 of a new sheet it creates, which has the sheet name matching the filename.txt.

What do I do to the code to just make it bring the info from the textfile and put it in cell A2?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
I'm not sure right off.
The example I posted should open the file specified in A1 of the active sheet & copy Sheet1, cell A2 of the newly opened file & paste it in D10 of the originally active sheet.

Can you post the actual code you're using?



Jindon:
You never fail to come up with something new and intriguing!
I’ll have to go through your code and try to figure it out.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,562
Members
410,560
Latest member
1ndependent
Top