Edgarvelez
Board Regular
- Joined
- Jun 6, 2019
- Messages
- 197
- Office Version
- 2016
- Platform
- 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
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