[Solved] VBA correction to save to specific folder

[Dumi_Bogdan]

Hi, can I please have your help to check this code?
My problem is that when it is supposed to save the file, instead of picking up the specific folder path I added in the VBA, it saves the file in the last folder/directory that I have opened, and then I have to locate it in the folders I accessed in the last hour
Many thanks in advance.

Sub SUBSaveNewStatement()
'
' Newstatement1 Macro
' copy and save to new file for a new account statement.
'

'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Set the output path and file name
outputPath = "C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements"
CustomerName = Range("B2").Value
CustomerNumber = Range("C2").Value
CustomerSite = Range("E2").Value
outputFileName = CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"

ActiveWorkbook.SaveAs filename:=outputFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Sub SvMe()
'Saves filename

Dim newFile As String, outputFileName As String

'Change the date format to whatever you'd like, but make sure it's in quotes
outputFileName = CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements"
ActiveWorkbook.SaveAs filename:=outputFileName

End Sub

End Sub

 

[Yousuf Shovon]

Hi, can I please have your help to check this code?
My problem is that when it is supposed to save the file, instead of picking up the specific folder path I added in the VBA, it saves the file in the last folder/directory that I have opened, and then I have to locate it in the folders I accessed in the last hour
Many thanks in advance.

Sub SUBSaveNewStatement()
'
' Newstatement1 Macro
' copy and save to new file for a new account statement.
'

'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Set the output path and file name
outputPath = "C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements"
CustomerName = Range("B2").Value
CustomerNumber = Range("C2").Value
CustomerSite = Range("E2").Value
outputFileName = CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"

ActiveWorkbook.SaveAs filename:=outputFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Sub SvMe()
'Saves filename

Dim newFile As String, outputFileName As String

'Change the date format to whatever you'd like, but make sure it's in quotes
outputFileName = CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements"
ActiveWorkbook.SaveAs filename:=outputFileName

End Sub

End Sub

Dear Dumi_Bogdan,
Welcome to our ExcelDemy forum! I understand you wish to modify the VBA code to save a file in a specific folder. After going through the given code, I think you are not including the complete file path in the OuputFileName. Instead of ChDir (which apparently has no relevance), concatenate this path to OuputFileName. Here is the modified code:

Sub SUBSaveNewStatement()
    '
    ' Newstatement1 Macro
    ' copy and save to new file for a new account statement.
    '
    
    '
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Set the output path and file name
    outputPath = "C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements\"
    CustomerName = Range("B2").Value
    CustomerNumber = Range("C2").Value
    CustomerSite = Range("E2").Value
    outputFileName = outputPath & CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"
    
    ActiveWorkbook.SaveAs filename:=outputFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Sub SvMe()
    'Saves filename
    
    Dim newFile As String, outputFileName As String
    
    'Change the date format to whatever you'd like, but make sure it's in quotes
    outputFileName = "C:\Users\bdumitrascu\OneDrive - PTC\Documents\Account statements\" & CustomerName & " - " & CustomerNumber & " - " & CustomerSite & " - Updated Statement - " & Format(Date, "dd-MMM-yyyy") & ".xlsx"
    
    ActiveWorkbook.SaveAs filename:=outputFileName

End Sub

Let me know if it works for you. Good Luck!

Regards,
Yousuf Shovon

 

[Dumi_Bogdan]

Hi,

this worked perfectly - thank you so much.

 

[Yousuf Shovon]

Hi,

this worked perfectly - thank you so much.

You are most welcome, Dumi_Bogdan. We are glad to be helpful to you as always.