Solved

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

Posted on 2014-09-11
11
224 Views
Last Modified: 2014-09-15
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
Comment
Question by:Tocogroup
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
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
 
LVL 26

Accepted Solution

by:
MacroShadow earned 250 total points
Comment Utility
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
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
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
 

Author Comment

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

Author Comment

by:Tocogroup
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 35

Assisted Solution

by:Kimputer
Kimputer earned 250 total points
Comment Utility
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
 

Author Comment

by:Tocogroup
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
NFP:

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

Author Comment

by:Tocogroup
Comment Utility
'RunTime error 76 Path not found' I meant to say.
0
 
LVL 35

Expert Comment

by:Kimputer
Comment Utility
Please use FULL path!
0
 

Author Closing Comment

by:Tocogroup
Comment Utility
Thanks to you both for assisting me with this. Consequently, I've split the points.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now