Check if file exists

[WembleyBear]

Hi

I'm fairly new to this & I've read a couple of similar posts but they don't
seem to answer my question fully. I am importing a csv file (c:\nlexp.csv)
from our Dealer Management system on a daily basis, however, if the export
from DMS is run more than once in the day, the exported file is renamed by
the DMS (c:\18nlexp.csv).

Before the file is imported, I need to:

1) Check if the file "C:\18nlexp.csv" exists. If it doesn't, then do nothing.
2) If it does exist, and the date on the file <> today, do nothing.
2) If it does exist, and the date on the file = today, then rename the file
to "C:\nlexp.csv" and continue with the import

I fine with renaming the file & the import, it's the checks I'm having a
problem with. Any help would be appreciated.


Many thanks
Martyn

Excel 2000, Windows Server 2003 over Citrix PS4

 

[Jon Peltier]

Try something like this:

dim sNewFile as String
dim sOldFile as String

sNewFille = "c:\nlexp.csv"
sOldFile = "c:\18nlexp.csv"

If Len(Dir(sOldFile)) > 0 Then
If Int(FileDateTime(sOldFile)) = Int(Now) Then
Name sOldFile sNewFile
'' continue with import
Kill sOldFile '' OPTIONAL
End If
End If

- Jon

 

[Joel]

the easies way of check if a file exists is to use dir(pathname\filename).
If it return an empty sting the the file doesn't exist. the dir will accept
a wildcard (*) as path of the filename.

exist = dir("c:\temp\book1.xls")
if exist <> "" then
msgbox("file Exists")
end if


You can get a file date using the following

Set fso = CreateObject _
("Scripting.FileSystemObject")

Set fs = fso.GetFile("c:\temp\book1.xls")
mydate = fs.DateLastModified

You can combine the two as follows

Set fso = CreateObject _
("Scripting.FileSystemObject")
exist = dir("c:\temp\book1.xls")
if exist = "" then
msgbox("file doesn't Exists")
else
Set fs = fso.GetFile("c:\temp\book1.xls")
mydate = fs.DateLastModified
msgbox("Last Modified : " & mydate)
end if

 

[WembleyBear]

Thanks very much Jon - that did the trick!

Martyn