Automate Import File process

[bigdars]

I have a directory D:\Monthly Log\ . This directory contains folder names like "2002 - 02 Feb" and "2002 - 03 Mar". What I want to do is when a user clicks a button on a form, have the folder that is the month previous to the current month and import a file called ahcnty.txt(ie: it is April now so if they click on the import button, the 2002 - 03 Mar folder would open and the ahcnty file would import). When the next month comes, the next monthly folder would automatically open and the ahcnty file would import when the button is selected. Any assistance is greatly appreciated.

 

[B827]

you coul try something like this, once working put in some error handling. You may need to modify the transfertext part to suit your situation.

Function FolderID() As String
Dim IDStr As String, mn As Integer, mon As Variant
mn = DatePart("m", Date)
If mn = 1 Then
IDStr = DatePart("yyyy", Date) - 1
mn = 12
Else
IDStr = DatePart("yyyy", Date)
mn = mn - 1
End If
mon = DateSerial(IDStr, mn, 1)
mon = Format(DateSerial(IDStr, mn, 1), "mmm&quot
If mn > 10 Then
IDStr = IDStr & " - " & mn
Else
IDStr = IDStr & " - 0" & mn
End If
FolderID = IDStr & " " & mon
End Function


Function ImportFile()
Dim Fileloc As String
Fileloc = FolderID()& "\ahcnty.txt"
DoCmd.TransferText acImportDelim, , , "Destination Table Name", Fileloc
End Function
Sandy

 

[bigdars]

Thanks a lot for the help...I will try this out as soon as I get a chance. Thank You!