Load spreadsheet with datecode with a click!

[hardrock]

Hey all! need some help with an addition to the code below. Basically, when a user clicks the "open" button on the form the spreadsheet named paul.xls in C:\test is loaded into an Access table called "works"

The bit of code works great, but i'd like to add the feature where the spreadsheet will only load if it is within a date range of say +2 days of the date at the end of the string and not before i.e

if i try and open Paul_050407.xls it can only load up between 05 and 06 of April only and not before. Any help apprecited!

Private Sub open_Click()
Dim ImportDir As String, ImportFile As String
ImportDir = "c:\test\"
ImportFile = Dir(ImportDir & "paul.xls")
Do While ImportFile <> ""
DoCmd.TransferSpreadsheet acImport, 8, "works", ImportDir & ImportFile, True, "A:I"
ImportFile = Dir
Loop

End Sub

 

[ted.martin]

I am sure there are other ways BUT I have just created a little form with 2 fields and a button.

DateText is the extracted Date portion of your file name (e.g.050407) and ActualDate is the converted date you need. If you play with this code, you should be able to get to a real date from your file name and then using an IF statement check it against the keyword Date() and so decide whether to open the file or not.

Private Sub Command_Click()

Dim str, str1, str2, str3 As String
Dim str4 As Date

datetext.SetFocus

str = datetext.Text

str1 = Left$(str, 2)
str2 = Mid$(str, 3, 2)
str3 = Right$(str, 2)

str4 = str1 & "/" & str2 & "/" & str3

ActualDate.SetFocus
ActualDate.Value = str4

End Sub

 

[hardrock]

It works!

hi ted, thats a great idea, and yes it works with the number string, i'm just struggling abit with a method to make access point to the c:\test directory and then point to find Paul* where *is the date code i.e Paul_050407.
Basically, to make it simple i want access to import a file with todays date only, i.e paul_050407.xls, if its say paul_040407.xls, i want a message box to say "File has expired"

Its a bit comlicated for a newbie, but i'm sure you or someone can help me out here

 

[DJkarl]

hi ted, thats a great idea, and yes it works with the number string, i'm just struggling abit with a method to make access point to the c:\test directory and then point to find Paul* where *is the date code i.e Paul_050407.
Basically, to make it simple i want access to import a file with todays date only, i.e paul_050407.xls, if its say paul_040407.xls, i want a message box to say "File has expired"

Its a bit comlicated for a newbie, but i'm sure you or someone can help me out here

I think something like this might be what you're looking for.

Sub DateTST2()
Dim ImportDir As String, ImportFile As String
Dim ImpDate As String
Dim ValDate As Date
Dim x As Long

ImportDir = "C:\temp\"
ImportFile = Dir(ImportDir & "paul*.xls")

Do While ImportFile <> ""
    x = InStrRev(ImportFile, "_", , vbTextCompare)
    ImpDate = Mid(ImportFile, x + 1, 6)
    ValDate = Format(Left(ImpDate, 2) & "/" & Mid(ImpDate, 3, 2) & "/" & Right(ImpDate, 2), "dd/mm/yy")

Select Case ValDate
    Case Is = Date
        'Import File Here
        DoCmd.TransferSpreadsheet acImport, 8, "works", ImportDir & ImportFile, True, "A:I"
    Case Is < Date
        MsgBox "File has expired."
    Case Else
        MsgBox "File too far in the future."
End Select
ImportFile = Dir
Loop

End Sub

 

[hardrock]

re:

Karl you are the best DJ dude!

Many thanks

 

[hardrock]

Updated form

I've used Djkarls code, and put it to use in a simple form i've attached. it will only allow paul to load a spreadsheet (paul_060407.xls) with todays date from c:\temp. Now the next part is for paul to enter some data in the form, i.e a new delivery date and comments and for him to save it and export it back to me in a new spreasheet. i.e (paul_060407_updated.xls)
Some additions:

1) it would be nice if the due date is in arreas to show in red (if thats poss).
2) if i send paul a new spreadsheet next week, i,e paul_130407.xls, it wont over write the comments / or new due date hes already entered from the previous week. But he can change the values!

Confusing or what! Any help appreciated for a total dum newbie!