Rx_
Nothing In Moderation
- Local time
- Today, 03:36
- Joined
- Oct 22, 2009
- Messages
- 2,803
Found several code samples to start. If anyone has suggestions for creating a multi-tier directory structure for a SaveAs - please chip in.
Sorry, had a last minute design change - nobody responded so will update the requirement and code. Will add more code as day progresses.
Output: General UNC Path + folderName with YEAR plus another folder name + ReportName Folder + Month Subfolder
Report Name is passed in as parameter - Month (03 - Mar) is dynamic
If a new folder is created, verify the folder is complete
Sorry, had a last minute design change - nobody responded so will update the requirement and code. Will add more code as day progresses.
Output: General UNC Path + folderName with YEAR plus another folder name + ReportName Folder + Month Subfolder
Report Name is passed in as parameter - Month (03 - Mar) is dynamic
If a new folder is created, verify the folder is complete
Code:
Public Const UNCReportPath As String = "[URL="file://\\Folder05\finance\"]\\Folder05\finance\[/URL]" ' leave room to add Year
Public Const UNCReportPathFolder As String = " FPA\Capital Reporting" ' Add 4 digit Year and Space then append this
' Note: Each Report will be followed by a \Report Name\ then have a folder or file added here
Public Function UNCPathSaveAsReport(Optional ReportFolderName As String = "Risk Assessment") As String ' First report is Risk Assessment
Dim SubFolderMonthName As String
Dim CurrentYear As String ' NOTE for testing Add 1 year then remove for delivery
Dim FullUNCPath As String
Dim FullUncPathToCreate As String
On Error GoTo err_Trap
' Use the current Date to evaluate if a new folder (e.g. 03-Mar) needs to be created - if folder exist, then continue
' Create today's Month string
SubFolderMonthName = CStr(Format(Month(Date), "00") & " - " & MonthName(Month(Date), True) & "")
CurrentYear = CStr(Format(Year(Date), "0000") + 1) ' <------------ CHANGE +1 to +0 used for Testing when Delivered into Production
FullUNCPath = UNCReportPath & CurrentYear & UNCReportPathFolder & SubFolderMonthName ' Test to see if exist or to create
FullUncPathToCreate = UNCReportPath & CurrentYear & UNCReportPathFolder ' If Path above doesn't exist - can use this to add subFolderMonthName
Debug.Print FullUNCPath
Debug.Print FullUncPathToCreate
If Dir(FullUNCPath, vbDirectory) = "" Then
Debug.Print "directory doesnt exist"
If Dir(FullUncPathToCreate, vbDirectory) = "" Then
Debug.Print "Something is very wrong - root path is not there"
Else
Debug.Print "Path exist but needs to have Sub Directory added"
MkDir FullUNCPath
If Dir(FullUNCPath, vbDirectory) = "" Then
Debug.Print "Something is wrong, the root path exist but the subdirecory couldn't be created"
' e.g. raise an error - perhaps the user doesn't have rights to create
Else
Debug.Print "Now the full directory exist and is ready to use"
End If
End If
Else
Debug.Print "Directory Alerady exist Please continue"
End If
FullUNCPath = FullUNCPath & "" ' add "" to be ready for a SaveAs
UNCPathSaveAsReport = FullUNCPath
Exit Function
err_Trap:
Debug.Print "Add error trap code for UNCPathSaveAsReport"
End Function
Last edited: