accessing a text file with a variable name

[Duritz]

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?

 

[HalfAce]

Hello Duritz,
Does this thread point you in the right direction?

http://www.mrexcel.com/board2/viewtopic.php?t=237242

 

[Duritz]

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]

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?

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]

Hi
Perhaps

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]

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]

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.