Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if file exists | Excel Discussion (Misc queries) | |||
File Exists Formula | Excel Worksheet Functions | |||
Determine if a File Exists | Excel Discussion (Misc queries) | |||
How to check if a file exists in an ftp folder | Excel Discussion (Misc queries) | |||
File Exists | Excel Discussion (Misc queries) |