Checking for file

Topic

New Reply

I’ve got a bit of code to save a ‘tab’ as a seperate file. No error checking as yet.

I’d like to build in a check to see if the file is there, before the resto the code runs. Of course if the file does exist, I’ll build in something else. Use the Save As, or something.

But I don’t know how

Below is the code snippet;

Sub Save_Tab1()

Dim MsgTit As String
Dim FPAth As String

MsgTit = ActiveSheet.Name & ” saved”
FPAth = “F:ISO 18001Legal ComplianceCompliance Audits”

ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:= _
FPAth & ActiveSheet.Name & ” ” & Format(Date, “MMYY”) & “.xls”, FileFormat:= _
xlExcel8, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False _
, CreateBackup:=False

ActiveWorkbook.Close

ActiveSheet.Select
Range(“B6:F50”).ClearContents
Range(“B3”).ClearContents
Range(“F3”).ClearContents

MsgBox “The file has now been saved”, vbOKOnly, MsgTit

End Sub

Ideas please

Reply | Quote

Replies

You can use the Dir function to check whether a file exists. In the following version I use a variable strFullName to store the path+filename.

Code:

Sub Save_Tab1()
  Dim MsgTit As String
  Dim FPAth As String
  Dim strFullName As String

  MsgTit = ActiveSheet.Name & " saved"
  FPAth = "F:ISO 18001Legal ComplianceCompliance Audits"
  strFullName = FPAth & ActiveSheet.Name & " " & Format(Date, "MMYY") & ".xls"
  If Dir(strFullName) = "" Then
	' File does not exist yet
	ActiveSheet.Copy
	ActiveWorkbook.SaveAs Filename:=strFullName

	ActiveWorkbook.Close

	ActiveSheet.Select
	Range("B6:F50").ClearContents
	Range("B3").ClearContents
	Range("F3").ClearContents

	MsgBox "The file has now been saved", vbOKOnly, MsgTit
  Else
	' File exists
	MsgBox "There is already a file '" & strFullName & "'.", vbExclamation
	' Other code goes here
	' ...
  End If
End Sub

Reply | Quote

That’s brilliant Hans & far more elegant than what I had in mind.

Cheers

Reply | Quote