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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,454
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,454
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.
 

Forum statistics

Threads
1,141,427
Messages
5,706,378
Members
421,446
Latest member
rena jhon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top