Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 18001\Legal Compliance\Compliance 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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 18001\Legal Compliance\Compliance 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

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's brilliant Hans & far more elegant than what I had in mind.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •