Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
Tocogroup
Asked:
Tocogroup
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
MacroShadowCommented:
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

0
 
MacroShadowCommented:
This is another:
Dim oFso As Object

Set oFso = CreateObject("Scripting.FileSystemObject")

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

Open in new window

0
 
KimputerCommented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TocogroupAuthor Commented:
Sorry, my fault. I should have said a folder with 'any' file in it. That is, I don't know the filename.
0
 
TocogroupAuthor Commented:
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 ?
0
 
KimputerCommented:
You loop through it here:

Sub test()

Set fs = CreateObject("Scripting.FileSystemObject")

Source = "c:\temp1"
Dest = "c:\temp2"
Set r = fs.GetFolder(Source)

For Each f In r.Files
    fs.CopyFile Source & "\" & f.Name, Dest & "\" & f.Name
    fs.DeleteFile Source & "\" & f.Name
Next


End Sub

Open in new window

0
 
TocogroupAuthor Commented:
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 ?
0
 
Rory ArchibaldCommented:
NFP:

You can just replace the Test.txt file name in MacroShadow's Dir code with *.*
0
 
TocogroupAuthor Commented:
'RunTime error 76 Path not found' I meant to say.
0
 
KimputerCommented:
Please use FULL path!
0
 
TocogroupAuthor Commented:
Thanks to you both for assisting me with this. Consequently, I've split the points.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now