Open import file by date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that opens an import file, formats the data contained in
the import file for export to our mainframe, and reports on the data. The
import files for this database are named by the date they are generated, i.e.
bd112805.ok. I want to be able to have Access automatically find and open
these files based on the current date. I also need to rename them from their
..ok extensions to a .txt so that they will open in Access XP (unless there's
a way to override Access and force it to import the files as they are now,
without renaming them). Copying the files is no big deal, except that I can't
figure out for to make it copy the file for the current day on it's own. My
command syntax is fine, but the filename changes each day. (I'm using
FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)
 
Dim strSourceName as String
Dim strDestinationName as Sting
Dim strFileDate as String

strFileDate = Format(Date(),"mmddyy")
strSourcName = "\\media\export\bd" & strFileDate & ".ok"
strDestinationName = "C:\bd" & strFileDate & ".txt"
FileCopy strSourceName, strDestinationName

One note. If it really is c:\, I would advice you never put anything in the
root directory of your C: drive.
 
That was the same idea I had, but also the same result: "Path/File Not
Found". I will just have to assign names to each file and copy them to their
assigned names. That should make it easier to import them as well. Thanks for
your help, at least now I know I was on the right track :)
 
There are only two reasons this should fail:
1. The path/file does not exist
2. The file is open
 
Well, after some investigation and some hair-pulling, I figured out why it
wasn't working. I had to hard-code each file into the vB code. Apparently the
FileCopy function doesn't like copying files to a directory, so I had to
hard-code filenames for each file:
***
Function GetImportFiles() As Variant
Dim strSourceName As String
Dim strDestinationName As String
Dim strFileDate As String
strFileDate = Format(Date, "mmddyy")
strSourcName = "\\media\export\bd" & strFileDate & ".ok"
strDestinationName = "C:\bdauto.txt"
FileCopy strSourcName, strDestinationName
strSourcName = "\\media\export\bdarw" & strFileDate & ".ok"
strDestinationName = "C:\bdarw.txt"
FileCopy strSourcName, strDestinationName
strSourcName = "\\media\export\bm" & strFileDate & ".ok"
strDestinationName = "C:\bdman.txt"
FileCopy strSourcName, strDestinationName
strSourcName = "\\media\export\cc" & strFileDate & ".ok"
strDestinationName = "C:\ccauto.txt"
FileCopy strSourcName, strDestinationName
End Function
***
which works out well because now I can automate the whole process from start
to finish without any user input at all. Thanks for the help!
 
Back
Top