I have not been able to figure out how to get VBA to set the Drive and Path where I want to save the file.
Here is a sample of my attempts.
- - - - - - - - My Current Code - - - - - - - - - - - - -
Sub Auto_Open()
'========================================================
' SaveAs to protect master document
'--------------------------------------------------------
On Error GoTo ENDTHIS
vCheck = ActiveWorkbook.Name
If Not vCheck Like "*Master*" Then Call mSetQrtr: Exit Sub
vAppName = "Urine Contamination Rates"
vDate = Date
vYear = InputBox("! ! ! Welcome to the " & vAppName & " Workbook" & vbCrLf + vbCrLf & " Please enter the Fiscal Year for which to Create this workbook.", "Create New Fiscal Year for this new Workbook", Year(vDate) + 1)
If StrPtr(vYear) = 0 Then Exit Sub
Sheets(1).Range("D1").Value = vYear
vFilePath = "L:\Micro\Urine Contamination Files\" '\\r04mwvnas21.v05.med.va.gov\mwv_groups\P&LMS\Micro\Urine Contamination Files\
vFileNm = vYear & "-FY " & vAppName & ".xlsm"
If vFileNm = vbNullString Then MsgBox "You have ended this Macro. Nothing has been done."
If vFileNm = vbNullString Then GoTo ENDTHIS
ChDrive "L"
ChDir vFilePath
Application.Dialogs(xlDialogSaveAs).Show vFileNm
ENDTHIS:
Range("A1").Select
Range("B5").Select
Call mSetQrtr
End Sub
- - - - - - - - - - - End of Sample Code - - - - - - -
Bookmarks