Open import file by date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nicholas Scarpinato - 28 Nov 2005 20:36 GMT

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".)

Reply to this Message

Klatuu - 28 Nov 2005 21:18 GMT

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.

> FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)

> 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
[quoted text clipped - 7 lines]
> command syntax is fine, but the filename changes each day. (I'm using
> FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)

Reply to this Message

Nicholas Scarpinato - 28 Nov 2005 21:46 GMT

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 :)

> Dim strSourceName as String
> Dim strDestinationName as Sting
[quoted text clipped - 21 lines]
> > command syntax is fine, but the filename changes each day. (I'm using
> > FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)

Reply to this Message

Klatuu - 28 Nov 2005 21:52 GMT

There are only two reasons this should fail:
1. The path/file does not exist
2. The file is open

> 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
[quoted text clipped - 26 lines]
> > > command syntax is fine, but the filename changes each day. (I'm using
> > > FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)

Reply to this Message

Nicholas Scarpinato - 30 Nov 2005 14:56 GMT

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!

> There are only two reasons this should fail:
> 1. The path/file does not exist
[quoted text clipped - 30 lines]
> > > > command syntax is fine, but the filename changes each day. (I'm using
> > > > FileCopy "\\media\export\bd112805.ok", "c:\bd112805.txt".)

Reply to this Message