If file exixts stop else Save As

[jsantos77]

Hi Guys with the help of someone a lot more clued up than me on excel macros i got this macro that is supouse to check if a file exists and then based on the result stop and display a message "File Exists" or Copy, paste and clear some data before saving as a name partly based on a cell data. This macro is not completly finished and i am a bit stuck as the result i am getting all the time is the message box saying the "file exists" even when this is not the case. I am pulling my hair now because i have tried all sorts.
Could anyone help out please.
May thanks
Jsantos77

Public Function FileExists(sFullPath As String) As Boolean
    If Not Dir(sFullPath, vbDirectory) = vbNullString Then FileExists = True
End Function
Public Sub TestFolderExistence()
 
    If FileExists("MyFolder & NewFileName" & ".xls") Then
        MsgBox "File exists!"
    Else: MkDir ("MyFolder & NewFileName" & ".xls")
    End If
End Sub
Public Sub TestFileExistenceF()
MyFolder = "C:\Users\Marta\Desktop\NPO\NPO_ "
NewFileName = Sheets("Hoja1").Range("A1").Value
Dim Sht As Variant
Dim Shts As Variant
Dim MyFolder As String
Dim NewFileName As String
 
  If FileExists("MyFolder & NewFileName" & ".xls") Then
    MsgBox "File exists!"
    Else
   Range("G6:G9").Copy
   Range("A6").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
 Range("B6:G9").ClearContents
 Range("B6").Select
 
Application.ScreenUpdating = True
 
    ActiveWorkbook.SaveAs _
       Filename:=MyFolder & NewFileName & ".xls", _
       Password:="", _
       WriteResPassword:="", _
       ReadOnlyRecommended:=False, _
       CreateBackup:=False
       Application.ScreenUpdating = True
End If
End Sub

 

[bensonsearch]

Hi,
What is NewFileName set to?

i cant find where its been set to anything

 

[jsantos77]

It's based on a value on that particular cell, this change as it refers to a financial week number.
Thanks for looking
Jsantos77

 

[jim may]

BenSearch is referring to the below procedure...

Public Sub TestFolderExistence()
 
    If FileExists("MyFolder & NewFileName" & ".xls") Then
        MsgBox "File exists!"
    Else: MkDir ("MyFolder & NewFileName" & ".xls")
    End If
End Sub

 

[jsantos77]

Not too sure what you mean but my intention was to set the workbook name to NPO_(contents of cell A1 on sheet (hoja1)).


C:\Users\Marta\Desktop\NPO\NPO_

Thanks for your patience
Jsantos77

 

[jsantos77]

Hi Guys

I think it makes more sence now but still doesn´t give me the result i expect, which basicaly is :If NewFileName exists display MsgBox and exit Sub, else Copy and paste as below and save as NewFileName.
All i want is to stop replacing files that exist.

Any improvement on these changes or am i still miles from getting there?

Public Function FileExists(sFullPath As String) As Boolean
    If Not Dir(sFullPath, vbDirectory) = vbNullString Then FileExists = True
End Function
 
Public Sub TestFolderExistence()
 
 MyFolder = Thisworkbook.path
NewFileName = NPO_ & Sheets("Hoja1").Range("A1").Value
 
    If FileExists(MyFolder & NewFileName & ".xls") Then
        MsgBox "File exists!"
    Else
   Range("G6:G9").Copy
   Range("A6").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
Range("B6:G9").ClearContents
Range("B6").Select
 
Application.ScreenUpdating = True
 
    ActiveWorkbook.SaveAs _
       Filename:=MyFolder & NewFileName & ".xls", _
       Password:="", _
       WriteResPassword:="", _
       ReadOnlyRecommended:=False, _
       CreateBackup:=False
       Application.ScreenUpdating = True
End If
End Sub

 

[bensonsearch]

Hi,
try putting a msgbox in the code here

MyFolder = Thisworkbook.path
NewFileName = NPO_ & Sheets("Hoja1").Range("A1").Value
 msgbox(NewFileName)
    If FileExists(MyFolder & NewFileName & ".xls") Then

and tell me what NewFileName is please. whatever the box pops up with is what it is looking for

 

[jsantos77]

Hi Bensonsearch

It´s working now, managed to get some extra help (Mudraker Thank You) still many thanks for your interest and help.
This is how it is now.
Thanks MUDRAKER

Public Function FileExists(sFullPath As String) As Boolean
 
    If Not Dir(sFullPath, vbDirectory) = vbNullString Then FileExists = True
End Function
Public Sub TestFileExistenceF()
Dim Sht As Variant
Dim Shts As Variant
Dim MyFolder As String
Dim NewFileName As String
MyFolder = ThisWorkbook.Path & "\" & FolderName
NewFileName = NPO_ & Sheets("Hoja1").Range("A1").Value
If FileExists(MyFolder & NewFileName & ".xls") Then
MsgBox "FILE EXISTS!!!!, Please CHANGE Week before continuing!"
Else
Range("A6:A9").Value = Range("G6:G9").Value
Range("B6:G9").ClearContents
Range("B6").Select
'Application.ScreenUpdating = True
ActiveWorkbook.SaveAs _
Filename:=MyFolder & NewFileName & ".xls", _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.ScreenUpdating = True
End If
End Sub

 

[jsantos77]

[closed] If file exixts stop else Save As

Thank You
[closed]