Import and Rename Unique Files

[Guest]

Hello all,

I was wondering if anyone knew of any way to select files to be
imported/linked based on file attributes (i.e. - date modified) rather than
by file name. I am trying to automate my database, however one of the source
files (a Peoplesoft query) is assigned a unique file name after each run. As
far as I know, there is no way to standardize the target query file, and
therefore, I am trying to use an alternate method for opening a file. I am
not even sure I understand what I wrote, but if you do, and you can help, let
me know.

Rick

 

[John Nurick]

Hi Rick,

If we're both on the same track, you need something like this, which
should return the first file it finds with the date you specify.

Function GetFileNameByDate(TheFolder As String, _
TheDate As Date) As String

Dim FSO As Object 'Scripting.FileSystemObject
Dim Fdr As Object 'Scripting.Folder
Dim Fil As Object 'Scripting.File
Dim FileDate As Date

GetFileWithDate = "" 'default value
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fdr = FSO.GetFolder(TheFolder)

For Each Fil In Fdr.Files
'convert timestamp property of Fil to a VBA Date value
FileDate = DateValue(CDate(Fil.DateLastModified))
If FileDate = TheDate Then
GetFileWithDate = Fil.Path
Exit For
End If
Next
End Function

Warning: I haven't tested it thoroughly. In particular, I'm not sure
that this
CDate(Fil.DateLastModified)
will work for all dates with all Windows regional or date/time settings.

 

[Guest]

Not knowing what the file name will be makes it a little difficult. I
thought about the DIR() function, but it wont give you enough info. I might
suggest you take a look at this site:

http://www.mvps.org/access/api/api0001.htm

It is an API call to the Windows Open File Dialog. It will allow you to
navigate to the folder and see the files. You can also select the display
format so you can see such things as date modified, etc. At least if a human
can guess what might be correct, then you can choose the file. It will return
the path and name of the file you can then pass to your import routine.

 

[Guest]

Klatuu - Thank you for your help!!

Upon running this code, could you tell me which variable will contain the
file name?

Rick

 

[Guest]

Thank you.

Hi Rick,

If we're both on the same track, you need something like this, which
should return the first file it finds with the date you specify.

Function GetFileNameByDate(TheFolder As String, _
TheDate As Date) As String

Dim FSO As Object 'Scripting.FileSystemObject
Dim Fdr As Object 'Scripting.Folder
Dim Fil As Object 'Scripting.File
Dim FileDate As Date

GetFileWithDate = "" 'default value
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fdr = FSO.GetFolder(TheFolder)

For Each Fil In Fdr.Files
'convert timestamp property of Fil to a VBA Date value
FileDate = DateValue(CDate(Fil.DateLastModified))
If FileDate = TheDate Then
GetFileWithDate = Fil.Path
Exit For
End If
Next
End Function

Warning: I haven't tested it thoroughly. In particular, I'm not sure
that this
CDate(Fil.DateLastModified)
will work for all dates with all Windows regional or date/time settings.

 

[Guest]

It doesn't really do that. What you do is call the GetOpenFile function and
it will present a dialog box that you can then navigate to the folder you
want and look at the file names. You will want to probably comment out or
change this line:

strfilter = ahtAddFilterItem(strfilter, _
"Access (*.mdb)", "*.MDB;*.MDA")

It restricts what you see in the dialog to only those file types. The
GetOpenFile function in what you downloaded is really only an example, but it
will give you are starting point. Comment out that line, and you will see
all file types. What happens then is you click on a file and it returns the
file name and path for the file you clicked on. It is just the Windows File
Open dialog. You will recognize it as soon as you see it. Try this. Open
your VBA Editor, select the module you have that code in and Comment out the
line of code above. Then go to your immediate window and type in:

X = GetOpenFile()

One the dialog pops up, navigate to a folder, and double click on any file.
The dialog will dissapear. Now type in:
?X
The path and name of the file you clicked on will show up.

I was suggesting this as a solution since you will not know the file name,
you should be able to determine which file is the one you want. If you click
on the view menu icon (rightmost on the tool bar), you can select Details,
and be able to see the file attributes.

Now, you can put the call to the GetOpenFile function in the click event of
a command button to do the import:

SomeVar = GetOpenFile
If SomeVar <> "" Then
DoCmd.TransferText acImportDelim, , "ImportTableName", SomeVar
End If

The acImportDelim may not be correct for your file type. You will have to
make changes to suit your situation. If, in fact, it is a text file, I would
recommend you do the import once by hand so you can set up an import
specification. Once you have done the File, Get External Data, Import and
selected a file, you will get an Import Wizard. Click on advanced. Here you
can specifiy field names and data types and which fields you want to imort.
Then click on Save As and give it a name. Then in your TransferText, you can
give it that name in the Specifation argument and it will really help get the
data in cleaner:

DoCmd.TransferText acImportDelim, MySpecName, "ImportTableName", SomeVar
Hope this will help.