asked on

How do I check for an existing file in VBA Excel ?

Hi All,

I am building a small Excel VBA application and I want to check if a Windows folder has a file in it. If it does then I want to process the file by copying it to another folder and then deleting it.

How should I begin to code this ?

Thanks
Toco

Joe Howard

This is one way:

If Dir("C:\Test\Test.txt") = "" Then
  MsgBox "File does not exist"
Else
  MsgBox "File exist"
End If

Open in new window

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kimputer

Another way:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FileExists("c:\a.txt") Then
    fs.CopyFile "c:\a.txt", "c:\temp\a.txt"
    fs.DeleteFile "c:\a.txt"
End If

End Sub

Open in new window

Tocogroup

ASKER

Sorry, my fault. I should have said a folder with 'any' file in it. That is, I don't know the filename.

Tocogroup

ASKER

So, I want to check to see if ANY file exists in that folder and if it does then I copy and delete it. How do I acquire its filename ?

SOLUTION

Kimputer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tocogroup

ASKER

I'm getting a 'RunTime error Path not found' message on the last line of the script that reads as follows:
Source = "\Input"
Dest = "\Archive"
Set r = fs.GetFolder(Source)

Is this because it doesn't use relative folder addresses ? Do I have to enter the full file path ? Or can I use a relative address ?

Rory Archibald

NFP:

You can just replace the Test.txt file name in MacroShadow's Dir code with *.*

Tocogroup

ASKER

'RunTime error 76 Path not found' I meant to say.

Kimputer

Please use FULL path!

Tocogroup

ASKER

Thanks to you both for assisting me with this. Consequently, I've split the points.