Same code works differently in a different folder ? (no fixed pathing in code)

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
So my code works differently in a the same file, but in a different file path


Sub SaveAsExample()


Code:
Dim FName           As String
Dim FPath           As String
On Error GoTo Handler:
    FPath = Sheets("Setup").Range("A1").Text
    FName = Sheets("Setup").Range("G1").Text
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName
    Exit Sub
Handler:
    FPath = Sheets("Setup").Range("A1").Text
    FName = Sheets("Setup").Range("G1").Text
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName & "_2"


End Sub

The way it should work:
First it saves the file based on date in cell and filepath in cell
IF the file already exists ,it naturally asks if I want to replace this file , if I say yes --it replaces. If I say no --it creates file name and adds _2.

BUT when all I do is paste this file to another folder ,it no longer asks me if I want to replace the file ,and adds _2 right away, if file name already exists.
Any ideas how is it possible ?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You should not use the instruction "On Error Goto", the ideal is to control the possible errors, since it could be that the folder is badly written, some file protected, etc.

Another recommendation is to create a copy, that way you keep your file open with the macro.



Try the following:

Code:
Sub savefile()
    Dim FName   As String
    Dim FPath   As String
    Dim wAns    As Variant
    
    FPath = Sheets("Setup").Range("A1").Text
    FName = Sheets("Setup").Range("G1").Text
    
    If Dir(FPath & "\" & FName & ".xlsm") <> "" Then
        wAns = MsgBox("The file already exists. Do You want to replace it", vbQuestion & vbYesNoCancel, "Save Copy")
        Select Case wAns
            Case vbYes
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".xlsm"
                Application.DisplayAlerts = True
            Case vbNo
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & "_2.xlsm"
                Application.DisplayAlerts = True
            Case vbCancel
                MsgBox "Process canceled"
        End Select
    Else
        ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".xlsm"
    End If


End Sub
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
It doesn't seem to work
It seems to try and find filename ,that doesn't exist yet.
I use xlsb format, so I changed xlsm to xlsb. Can this be a problem ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
No problem, you only change xlsm by xlsb on all lines.
If the file does not exist, then create it.



Code:
Sub savefile()
    Dim FName   As String
    Dim FPath   As String
    Dim wAns    As Variant
    
    FPath = Sheets("Setup").Range("A1").Text
    FName = Sheets("Setup").Range("G1").Text
    
    If Dir(FPath & "\" & FName & ".[COLOR=#0000ff]xlsb[/COLOR]) <> "" Then
        wAns = MsgBox("The file already exists. Do You want to replace it", vbQuestion & vbYesNoCancel, "Save Copy")
        Select Case wAns
            Case vbYes
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".[COLOR=#0000ff]xlsb[/COLOR]"
                Application.DisplayAlerts = True
            Case vbNo
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & "_2.[COLOR=#0000ff]xlsb[/COLOR]"
                Application.DisplayAlerts = True
            Case vbCancel
                MsgBox "Process canceled"
        End Select
    Else
        'If the file does not exist, then create it.
[COLOR=#0000ff]        ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".xlsb"[/COLOR]
    End If




End Sub
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

To clarify
This line
Code:
[COLOR=#333333]ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".xlsb"[/COLOR]
should create a new file right ?

That code didn't work for me, however when I changed SaveCopyAs to SaveAs, it mostly worked except when I clicked "Yes" on already existing filename (while it is supposed to replace file)
Thanks for taking time
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
To clarify
This line
Code:
[COLOR=#333333]ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & ".xlsb"[/COLOR]
should create a new file right ?

Yes.

---


Could you put exactly what you have in cells A1 and G1?
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Sure
A1 K:\Maint\PLANING\DSC CALENDARS 2019\FEB\experiment

G1 Friday, February 1,2019

Both of them are formula based.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If G1 is a Date, then

Code:
Sub savefile()
    Dim FName   As String
    Dim FPath   As String
    Dim wAns    As Variant
[COLOR=#0000ff]    Dim FExt    As String[/COLOR]
    
    FPath = Sheets("Setup").Range("A1").Value
[COLOR=#0000ff]    FName = Format(Sheets("Setup").Range("G1").Value, "mm-dd-yyyy")[/COLOR]
[COLOR=#0000ff]    FExt = ".xlsb"[/COLOR]
    
[COLOR=#0000ff]    If Dir(FPath, vbDirectory) = "" Then[/COLOR]
[COLOR=#0000ff]        MsgBox "The folder not exists", vbCritical[/COLOR]
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
        
[COLOR=#0000ff]    If Dir(FPath & "\" & FName & FExt) <> "" Then[/COLOR]
        wAns = MsgBox("The file already exists. Do You want to replace it", vbQuestion & vbYesNoCancel, "Save Copy")
        Select Case wAns
            Case vbYes
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & [COLOR=#0000ff]FExt[/COLOR]
                Application.DisplayAlerts = True
            Case vbNo
                Application.DisplayAlerts = False
                ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & "_2" & [COLOR=#0000ff]FExt[/COLOR]
                Application.DisplayAlerts = True
            Case vbCancel
                MsgBox "Process canceled"
        End Select
    Else
        ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName & [COLOR=#0000ff]FExt[/COLOR]
    End If
End Sub
 

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
I think it is best to explain what this macro purpose is, however if you don't find this info necessary I put my feedback to code to the end.
This workbook is a Template for day-to-day created workbooks by 2 persons.
They both work in Template and have their separate worksheets.
Now one person can start working in Template, then get interrupted and leave,
Then 2nd person finishes his part and then creates a workbook for current date
Now 1st person comes back, finishes and realizes file already exists, then he saves file as current date_2 (which is not good but ok)
All these day-to-day files are gathered into central file which has a formula like ='K:\Maint\PLANING\DSC CALENDARS 2019\FEB\[Friday, Feb 01, 2019.xlsb]Setup'!$G$13 for each day+'K:\Maint\PLANING\DSC CALENDARS 2019\FEB\[Friday, Feb 01, 2019_2.xlsb]Setup'!$G$13 for each day
Thus if file name changes due to human mistake it doesn't go into stats.
So the purpose is to standardize file names.

Feedback to code

SaveCopyAs still shows an error, but when I change it to SaveAs it works with the following issues: When I save the file (thus creating it) It will not replace itself (when I hit yes replace).


Code:
Sub savefile()
    Dim FName   As String
    Dim FPath   As String
    Dim wAns    As Variant
[COLOR=#0000ff]    Dim FExt    As String[/COLOR]
    
    FPath = Sheets("Setup").Range("A1").Value
[COLOR=#0000ff]    FName = Format(Sheets("Setup").Range("G1").Value, "[/COLOR]ddd, mmm dd, yyyy[COLOR=#0000ff]")[/COLOR]
[COLOR=#0000ff]    FExt = ".xlsb"[/COLOR]
    
[COLOR=#0000ff]    If Dir(FPath, vbDirectory) = "" Then[/COLOR]
[COLOR=#0000ff]        MsgBox "The folder not exists", vbCritical[/COLOR]
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
        
[COLOR=#0000ff]    If Dir(FPath & "" & FName & FExt) <> "" Then[/COLOR]
        wAns = MsgBox("The file already exists. Do You want to replace it", vbQuestion & vbYesNoCancel, "Save Copy")
        Select Case wAns
            Case vbYes
                Application.DisplayAlerts = False
                ThisWorkbook.SaveAs Filename:=FPath & "" & FName & [COLOR=#0000ff]FExt[/COLOR]
                Application.DisplayAlerts = True
            Case vbNo
                Application.DisplayAlerts = False
                ThisWorkbook.SaveAs Filename:=FPath & "" & FName & "_2" & [COLOR=#0000ff]FExt[/COLOR]
                Application.DisplayAlerts = True
            Case vbCancel
                MsgBox "Process canceled"
        End Select
    Else
        ThisWorkbook.SaveAs Filename:=FPath & "" & FName & [COLOR=#0000ff]FExt[/COLOR]
    End If
End Sub
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
But you eliminated the Backslash on all the lines

If you have this without Backslash at the end of the line
K:\Maint\PLANING\DSC CALENDARS 2019\FEB\experiment

You need the backslash to save the file.
ThisWorkbook.SaveAs Filename:="K:\Maint\PLANING\DSC CALENDARS 2019\FEB\experiment" & "\" & "vie, feb 01, 2019" & ".xlsb"

Dear, that's why you have an error.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,965
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top