Test if file exists
 

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

Test if file exists
 

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 wrote:

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

Test if file exists
 

Dave, thanks so much - perfect

"Dave Peterson" wrote:

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 wrote:

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