Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
™*Modify my code: Need to qualify FolderDialog selection
Hi -
The code below is used by several procedures within a Tool I'm currently developing. The code works well, but during End-User testing, a 'hole' has been discovered -- the user can select a folder containing old data, which will continue to work because these old files are the proper format. So, I'd like to add a 'quality check' of sorts -- one thought would be to check the 'modified date' on the first workbook in the folder and to throw a msgbox if it's more than 180 days older than the current date. it's only necessary to check one workbook, as other workbooks in the folder will be from the same month. I'm not sure what the coding to do this looks like ... Can you help? I've indicated below where I think this quality check should go, but am certainly open to other ideas .. Thanks very much for your time! Regards, Ray Sub PickPath() Dim Flags As Long, DoCenter As Boolean, Preset As String, Base As String Flags = BIF_RETURNONLYFSDIRS Flags = Flags + BIF_NEWDIALOGSTYLE Preset = Sheet6.Range("F17").Value 'If Len(Preset) 0 Then If Len(Dir(Preset)) = 0 Then Base = Preset 'End If Else Base = CurDir End If RetStr = GetDirectory(Base, Flags, DoCenter, "Please select a location to import from ...") If RetStr = "" Then MsgBox "No path selected ... update cancelled!" Exit Sub End If ' Quality-check code here ' If modified-date < ( current-date - 180) ' Then msgbox "data too old" ' exit sub ' End if ' End of Quality-Check Code Sheet6.Range("F17").Value = RetStr End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
?Modify my code: Need to qualify FolderDialog selection
You can use the FileDateTime function to get the file's last modification time (in the local time zone). E.g., Dim ModDate As Date Dim FolderName As String Dim FName As String FolderName = "C:" '<<< get directory name FName = Dir(FolderName & "\*.xls", vbNormal) If FName < vbNullString Then FName = FolderName & "\" & FName Else Exit Sub End If ModDate = FileDateTime(FName) If Now - ModDate = 180 Then Debug.Print FName, ModDate, "old" Else Debug.Print FName, ModDate, "ok" End If Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Mon, 22 Feb 2010 08:42:23 -0800 (PST), Ray wrote: Hi - The code below is used by several procedures within a Tool I'm currently developing. The code works well, but during End-User testing, a 'hole' has been discovered -- the user can select a folder containing old data, which will continue to work because these old files are the proper format. So, I'd like to add a 'quality check' of sorts -- one thought would be to check the 'modified date' on the first workbook in the folder and to throw a msgbox if it's more than 180 days older than the current date. it's only necessary to check one workbook, as other workbooks in the folder will be from the same month. I'm not sure what the coding to do this looks like ... Can you help? I've indicated below where I think this quality check should go, but am certainly open to other ideas .. Thanks very much for your time! Regards, Ray Sub PickPath() Dim Flags As Long, DoCenter As Boolean, Preset As String, Base As String Flags = BIF_RETURNONLYFSDIRS Flags = Flags + BIF_NEWDIALOGSTYLE Preset = Sheet6.Range("F17").Value 'If Len(Preset) 0 Then If Len(Dir(Preset)) = 0 Then Base = Preset 'End If Else Base = CurDir End If RetStr = GetDirectory(Base, Flags, DoCenter, "Please select a location to import from ...") If RetStr = "" Then MsgBox "No path selected ... update cancelled!" Exit Sub End If ' Quality-check code here ' If modified-date < ( current-date - 180) ' Then msgbox "data too old" ' exit sub ' End if ' End of Quality-Check Code Sheet6.Range("F17").Value = RetStr End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
?Modify my code: Need to qualify FolderDialog selection
Thanks alot Chip .... a big time-saver!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
Modify each cell in selection | Excel Programming | |||
VBA - Modify data in selection | Excel Programming | |||
Modify existing code to dynamic code | Excel Programming | |||
Modify a calculated field formula depending upon selection of pagefield | Excel Discussion (Misc queries) |