vba testing if a file exists

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Hi,

I've used the following code to test if a file exists before copying it to somewhere else but it keeps giving me an error saying.

"Expected end sub"


This is the code


Code:
Private Sub CommandButton1_Click()

Function FileExists(FileName As String) As Boolean
     FileThere = (Dir(FileName) > "")
End Function

If FileExists("C:\Archive.pst") Then

CommandButton1.Caption = "Please wait..."
CommandButton1.Enabled = False
CommandButton2.Enabled = False
FileCopy "C:\Archive.pst", "M:\email\Archive.pst"

Else
     MsgBox "File not found!", vbOKOnly, "Error!"
End If

End Sub

Any ideas?

Cheers
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

yoko

Active Member
Joined
Sep 5, 2006
Messages
349
Also is there a way to say that once the file has been copied a message pops up saying "done" or something.

Cheers
 

Wolfshead

Board Regular
Joined
Oct 18, 2006
Messages
100
Hi
You have got our function procedure inside the code of the sub procedure. Cut:
Function FileExists(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function
and paste it after the End Sub statement

Wolfshead
 

Forum statistics

Threads
1,136,877
Messages
5,678,290
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top