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