VBA - Prompt To Save Excel File In Variable Location

[garak0410]

I have some legacy code I am still trying to keep alive...when we save the file as a PDF via a Macro, it creates the proper directory based upon Job Number and saves a PDF there through an anging (but functional) PDF creator program...(code below)...




Dim BackToSheet As Worksheet
Const sDefaultCOPath As String = "z:\Change_Orders"

Sub email_Selected()
'Email_Selected Macro
' Macro recorded 1/14/2015 by Brian
'Keyboard Shortcut: Ctrl+Shift+E
'Application.CommandBars("Stop Recording").Visible = False
' Application.Goto Reference:="email_Selected"
'-- Do NOT Save Workbook because if they are in the emplate it can cause problems. ActiveWorkbook.Save
Dim spdfname As String
Dim sPDFNameDir As String
Dim spdfpath As String
Dim pos As Integer
Set BackToSheet = ActiveWorkbook.ActiveSheet ' set current sheet so we can come back to it
'-- First lets get the PDF File Name
Sheets("Form").Select
spdfname = Trim$(Cells(4, 3).Value)
If Len(spdfname) = 0 Then Exit Sub ' nothing to print
pos = InStr(spdfname, ".") 'test for an extension and remove if there
If pos > 0 Then
spdfname = Left$(spdfname, pos - 1)
End If
pos = InStr(spdfname, "-") 'test for an extension and remove if there
If pos > 0 Then
sPDFNameDir = Left$(spdfname, pos - 1)
Else
sPDFNameDir = spdfname
End If
Dim sMessageSubject As String, sMessageBody As String
sMessageSubject = "Change Order - " & spdfname
sMessageBody = "Change Order - " & spdfname & " has been attached for your review."
spdfpath = sDefaultCOPath & Application.PathSeparator & sPDFNameDir & Application.PathSeparator
'Look for Directory and create it if it does not exist
MakeDir (spdfpath)
Call PrintToPDFandEMAIL(True, True, BackToSheet.Name, spdfname, spdfpath, , sMessageSubject, sMessageBody)
' Reactivate original sheet
BackToSheet.Activate
End Sub

In additional to saving the PDF in that folder in the variable, we need it to save the Excel,file too...

I can get around VBA but often struggle in creating new code...how can I make it also save the file as an excel file in same directory?

Thanks!

[garak0410]

Is there just an easy line of code I can put in to prompt (or silently) save in the directory by this variable? sDefaultCOPath As String = "z:\Change_Orders"​

[georgiboy]

Maybe this will help:

Sub test()
Dim sDefaultCOPath As String

sDefaultCOPath = "z:\Change_Orders\"

Application.DisplayAlerts = False
ThisWorkbook.SaveAs sDefaultCOPath & "FileName.xlsx"
Application.DisplayAlerts = True
End Sub