Find Folder based on date.
I am trying to create a macro to open a file that is in a folder that is named based on a date (DD-MM-YY) - we will just call the folder 24-04-11 as it will help with the example. the goal is to open the folder if it is dated the last sunday that just passed. Assuming that we run the macro on the immediate monday through sunday following the target date. The folders are created the following day - a folder dated Sunday will be created on Monday and so forth. the following week, the folder to open would be 01-05-11, and so forth.
In finding the path, there are two issues. The first issue is different operating systems (XP, Vista and Windows 7), but the folder will always reside in the user profile's documents as C:\...\My Profile\ Document\Main\Folder-1\Attachments\04-24-11. In some operating systems, the user profile has their documents folder called "Documents" or "My Documents" or some variation depending on the O/S - I forget what Vista uses. The second challange that i have is the folder called "Folder-1". This folder will always have the same text, but the spacing or special characters is not always a given. The check to find the folder would be only the text "folder1", CAPS, special characters, spacing needs to be ignored.
i thought of the following for getting down past the user profile's documents, but still coming up blank for the rest:
dim sPathUser as String
bunch of code goes here
sPathUser = Environ$("USERNAME") & "\" & * "documents\Main\" & need some way to capture Folder-1 and ignore spacing and special characters, etc & "\Attachments\" & need some way to capture the preceeding Sunday folder
bunch of code here to open the file and capture the data, etc.
any suggestions would be helpful