Test if file exists

[Daniel Bonallack]

From Excel, I would like to test if a txt file is in a certain folder.

For example, I would like the simple code to know if mytext.txt is in folder
L:\Storage\

Secondly, I have the line to open a text file to a worksheet

Workbooks.OpenText Filename:="L:\Storage\myText.xls"

But if I want that to be imported to the active worksheet, how do I do that?

Thanks in advance!

regards
Daniel Bonallack

 

[Dave Peterson]

Dim DestCell as range
dim TestStr as string

set destcell = activesheet.range("a2") 'whereever you want

teststr = ""
on error resume next
teststr = dir("L:\Storage\myText.txt")
on error goto 0

if teststr = "" then
msgbox "It's not there"
else
workbooks.opentext filename:="L:\Storage\myText.txt"
activesheet.usedrange.copy _
destination:=destcell
activeworkbook.close savechanges:=false
end if

============
You could also record a macro when you do Data|Import external data (xl2003
menus). You'll be able to specify where you want the data to go. But you'd
still need to incorporate the test into that recorded macro.

 

[Daniel Bonallack]

Dave, thanks so much - perfect

Dim DestCell as range
dim TestStr as string

set destcell = activesheet.range("a2") 'whereever you want

teststr = ""
on error resume next
teststr = dir("L:\Storage\myText.txt")
on error goto 0

if teststr = "" then
msgbox "It's not there"
else
workbooks.opentext filename:="L:\Storage\myText.txt"
activesheet.usedrange.copy _
destination:=destcell
activeworkbook.close savechanges:=false
end if

============
You could also record a macro when you do Data|Import external data (xl2003
menus). You'll be able to specify where you want the data to go. But you'd
still need to incorporate the test into that recorded macro.