ActiveWorkbook.SaveAs to the same file path of worbook

Edgarvelez

Board Regular
Joined
Jun 6, 2019
Messages
84
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jun 6, 2019
Messages
84
Office Version
  1. 2016
Platform
  1. Windows
I removed
VBA Code:
    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
VBA Code:
    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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,127,765
Messages
5,626,738
Members
416,201
Latest member
brianhf

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
Top