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

S

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 ?
 

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
Sort by date Sort by votes
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Sure
A1 K:\Maint\PLANING\DSC CALENDARS 2019\FEB\experiment

G1 Friday, February 1,2019

Both of them are formula based.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA just isn't saving files to folder.
Replies
3
Views
931
Micron
M
B
  • Question Question
Daily file archive with date - Assistance with VBA to not overwrite previously save same filename
Replies
9
Views
362
brianv
B
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
574
Joe4
Joe4
B
  • Question Question
VBA to extract data from multiple folders and add to a master workbook plus identifying tags
Replies
2
Views
1K
John_w
J
S
  • Solved
Add a sequence to the workbook if the same name is found when saving
Replies
8
Views
554
Alex Blakenburg
A

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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
Back
Top