ActiveWorkbook.SaveAs to the same file path of worbook

[Edgarvelez]

Hi All,

I have this code that works good so foar but I have found 2 changes that I would like to do.

1. It saves to a specific file path and some times i have to move the macro to a different folder or my colleague borrow it and the file path has to changed.
fpath = "\\ACWFSPRDASC01.accessworld.local\FLDRREDIUSA$\Edgar.Velez\Desktop\INTAKE MACRO"
Would like it to save to the same location as the macro

As it saves it opens the file which is fine for final review which is good.

2. When it saves it also checks if the file name alread exists and if it does it pops a message box wich is good
Would like a button on the message box to override the existing file and save this new one.

Below is the full code.


Sub Macro5ExportUpLoadSht()
'
' Macro5ExportUpLoadSht Macro
'

'
Sheets("Intake Macro").Select
Range("A1").Select
Sheets("Intake Macro").Select
Range("A1").Select
Dim fname As String
Dim fpath As String
Dim name As String
Dim ws As Worksheet

'_________________In the line below in between the "" is where you enter the destination folder where the file gets exported to"____________
fpath = "\\ACWFSPRDASC01.accessworld.local\FLDRREDIUSA$\Edgar.Velez\Desktop\INTAKE MACRO"
fname = Range("D13") & " " & Range("D14") & " " & Range("D8") & ".xlsx"
name = Range("D13").Value
On Error Resume Next
Set ws = ThisWorkbook.Sheets("UPLOAD SHEET")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "sheet doesn't exist"
Exit Sub
End If
If Dir(fpath & "\" & fname) = vbNullString Then
ThisWorkbook.Sheets("UPLOAD SHEET").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\" & fname
Else
MsgBox "STOP!" & Chr(10) & Chr(10) & fname & Chr(10) & "This File Name Already Exists In The Folder" & Chr(10) & Chr(10) & "RENAME OR DELETE THE FILE IN THE FOLDER"
End If
End Sub

 

[Edgarvelez]

I removed

    fname = ActiveWorkbook.Path

from your code, the file path you had already given me in the beginning and I put it at the top of my code and there was the problem I had 2 (ActiveWorkbook.Path)
one under fpath= and the other under fname =
but you had told me what the problem was when you said "Looks like you are doubling up your file path there. " I didn't bother to look and just chucked it in there and clicked GO.

All is good now and it works great, exactly what I wanted thank you so much.

Also thanks for pointing out

    name = Range("D14").Value

don't know what that line of code was doing there, but I got rid of it.
Once again thanks for your help

 

[Joe4]

You are welcome.