Module assistance

[nyrob609]

Hi,

I need your assistance I have an appointment report that gets dumped daily to shared drive. This report holds the next day appointments. The report doesn't have an file extension so I created a command button that will run the module below giving the file a TxT extension and also imports the file to the access database.

I had to do the command button because my company IT department won't allow us to use the Schedule task function which it would've been easy to set up and run the macro and module everyday.

This was works great and easy for the user but on the weekends while nobody is here to press that button the system continues to dump the files creating multiple files with the same name just a difference in the extension given as shown below.

Friday report: RIMMARY D.000
Saturday: RIMMARY D.001
Sunday: RIMMARY D.002

So, I am having problems on Monday because Monday's appointment data is really in the report that comes out on Sunday but based on the module I wrote is looking for "RIMMARY D.000" which will be blank and if the file can't be find the debug dialog box file comes up.

What I would like to do is try IF and Then statement if possible that if there is no RIMMARY D.000 then use RIMMARY D.002.

Hopefully I made my self clear enought I want to be able to pull either file into database.

Thank you for your assistance.


Public Function ImportNonTXT()
Dim fs, Fn, FLength, Fext, FDot, FOrig

Set fs = CreateObject("Scripting.FileSystemObject")
Set Fn = fs.Getfile("H:\RIMARY D.000")

' This holds the file's original name for rename later.
FOrig = "RIMARY D"
' Get the length of the file name.
FLength = 8
' Set this value = to the last four characters of the file name.
Fext = Right("RY D", 4)
' Set this = to the first character.
FDot = Left("R", 1)

' If there is a dot in the fourth from the last position...
If FDot = "." Then
'... and the extension is not .txt.
If Fext <> ".txt" Then
' Remove the extension from the file name.
Fn.Name = Left("RIMARY D", (FLength - 4))
' Add the .txt to the file name.
Fn.Name = "RIMARY D" & ".txt"
End If
Else
' If there is not a dot in the fourth position
' add the .txt extension.
Fn.Name = "RIMARY D" & ".txt"
End If

' Transfer the file to a new table.
DoCmd.TransferText acImportDelim, "", "Tbl_Text", "H:\Rimary D.txt", False, ""
' After the file is transferred, rename it back to
' its original name.
Fn.Name = FOrig
End Function

 

[CraigDolphin]

A slightly different wya of doing it would be to do something like:

If Weekday(Date) = 2 then
  Set Fn = fs.Getfile("H:\RIMARY D.002") 
Else
  Set Fn = fs.Getfile("H:\RIMARY D.000") 
End if

That way, if the day is a monday it looks for the other version. Otherwise, it uses the standard version.

Not exactly what you were after but it might be a temporary workaround.

 

[boblarson]

You might want to have it use

If Format(Date,"dddd") = "Monday" Then


Because that will take care of any regions that might not have Sunday as day 1.

 

[CraigDolphin]

Good point