Transfer Text - Variable File Names

[CC]

I need to import text files on a daily basis. The text files are created
from another application and assigned file names that increments each day.
I'm importing them now by changing the file name to a static name that is
defined in the macro. I'm using Access 2003. Is there a way to tell Access
what file name to expect each day?

 

[Dirk Goldgar]

I need to import text files on a daily basis. The text files are created
from another application and assigned file names that increments each day.
I'm importing them now by changing the file name to a static name that is
defined in the macro. I'm using Access 2003. Is there a way to tell
Access
what file name to expect each day?

If the file names are predictable you just have to program it. Or if it's
the latest file in a folder that matches some pattern, you could get it by
examining the files in the folder. Could you give some examples of the file
names and how you would know which is the right file to import?

 

[CC]

Yesterday's file name was F2805V00. Today's filename is F2806V00. The 5th
character alwasy increments by 1.

I FTP the files from an FTP site and save them as a text file. I assume
Access cannot import the file before I save it as a Notepad file. The best
case scenario would be to have Access pick the file up directly from the FTP
site. Is that possible?

 

[John W. Vinson]

Yesterday's file name was F2805V00. Today's filename is F2806V00. The 5th
character alwasy increments by 1.

I FTP the files from an FTP site and save them as a text file. I assume
Access cannot import the file before I save it as a Notepad file. The best
case scenario would be to have Access pick the file up directly from the FTP
site. Is that possible?

Does the *fifth charcter* increment? Or does the four-digit number increment?
I.e. what would be today's filename if yesterday's were F2999V00?

I would suspect that you would need to store the filename in some table,
extract the portion which increments, and reconstruct a string to create the
new filename. You'ld also want code to handle cases where a filename got
skipped or needed to be reloaded.

 

[Dirk Goldgar]

Yesterday's file name was F2805V00. Today's filename is F2806V00. The
5th
character alwasy increments by 1.

It doesn't sound to me as though it's completely trivial to determine which
file you want to import. At best, as John Vinson said in his post, you'd
need to save the name of the last file imported and use that to calculate
the next name; but you'd also probably have to allow the user to override
the calculated filename.

It would be possible to use the Dir() function in combination with the
FileDateTime() function to find the most recently modified file in the
folder that matches the "F????V??" pattern. Then you could either import
that one, or propose it to the user and let the user override it if need be.
You might use the Windows GetOpenFile API to present the files for choosing,
using code like that posted at http://www.mvps.org/access/api/api0001.htm .

I FTP the files from an FTP site and save them as a text file. I assume
Access cannot import the file before I save it as a Notepad file. The
best
case scenario would be to have Access pick the file up directly from the
FTP
site. Is that possible?

It is possible to FTP from Access, though the code is a bit complicated if
you want to do anything more than the most rudimentary operation. There's
an Internet Data Transfer library posted here:
http://www.mvps.org/access/modules/mdl0037.htm . There's other freeware FTP
code available at various places on the web.

 

[CC]

I think the Internet Data Transfer library will require quite a learning
curve for me. I decided to import all files from the site & only use the
one(s) I need. My code works until I get to:

DoCmd.TransferText (acImportDelim), , "TableOne", "strFileName", False
End If
Loop

I get the Run Time error 31519 with the message "You cannot import this
file" Do you know what causes this error?

 

[Dirk Goldgar]

I think the Internet Data Transfer library will require quite a learning
curve for me. I decided to import all files from the site & only use the
one(s) I need. My code works until I get to:

DoCmd.TransferText (acImportDelim), , "TableOne", "strFileName", False
End If
Loop

I get the Run Time error 31519 with the message "You cannot import this
file" Do you know what causes this error?

Is that your actual code? If so, then your quotes around "strFileName" are
probably wrong. I would expect strFileName to be a variable holding the
name of the file to be imported, in which case you need to specify it like
this:

DoCmd.TransferText acImportDelim, , "TableOne", strFileName, False

I also removed the unnecessary parentheses around acImportDelim.

What is the actual name (including extension) of the file you're trying to
import? Only certain file-extensions are accepted for import. If yours
isn't, you can either rename the file each time (which you can do in code),
or you can edit the system registry to add the extension to the list of
acceptable extensions.

 

[CC]

Thanks for correcting the code, but it's not my code. I used code from
another post from William - he did not make the errors with the quotes.

Now I get run time error 3011 "The MS database engine could not find object
'F2807V00.txt'. I don't understand why Access cannot find the file b/c an
earlier line in the code displays a MsgBox displaying the correct file name,
so I know Access is seeing the file. Any ideas?

 

[Dirk Goldgar]

Now I get run time error 3011 "The MS database engine could not find
object
'F2807V00.txt'. I don't understand why Access cannot find the file b/c an
earlier line in the code displays a MsgBox displaying the correct file
name,
so I know Access is seeing the file. Any ideas?

Did you specify the full path to the file in strFileName, or just the name
of the file? You have to give the full path.

 

[CC]

I did specify the entire file name. Here is the code:

Private Sub Command47_Click()

Dim strFileName As String
strFileName = "a" 'Need to set file name to a non-blank value so
we go into the loop.

Do While strFileName <> ""
strFileName = Dir("C:\Documents and Settings\user1\Desktop\Temp
Files\ImportTesting\*.txt")
If strFileName <> "" Then 'A file was found
MsgBox strFileName

DoCmd.TransferText acImportDelim, , "TableOne", strFileName, False
End If
Loop

MsgBox "Done"

End Sub

 

[Douglas J. Steele]

The Dir function only returns the file name, not the file name and path.

You also need to put your initial Dir call outside of the loop. If you
don't, you'll get the same file every time:

Private Sub Command47_Click()

Dim strFolderName As String
Dim strFileName As String

strFolderName = "C:\Documents and Settings\user1\Desktop\Temp
Files\ImportTesting\"
strFileName = Dir(strFolderName & "*.txt")

Do While Len(strFileName) > 0
MsgBox strFileName
DoCmd.TransferText acImportDelim, , "TableOne", strFolderName &
strFileName, False
strFileName = Dir()
Loop

MsgBox "Done"

End Sub

 

[Dirk Goldgar]

I did specify the entire file name. Here is the code:

Private Sub Command47_Click()

Dim strFileName As String
strFileName = "a" 'Need to set file name to a non-blank value so
we go into the loop.

Do While strFileName <> ""
strFileName = Dir("C:\Documents and Settings\user1\Desktop\Temp
Files\ImportTesting\*.txt")
If strFileName <> "" Then 'A file was found
MsgBox strFileName

DoCmd.TransferText acImportDelim, , "TableOne", strFileName,
False
End If
Loop

MsgBox "Done"

End Sub

Sorry, but you didn't. The return value of the Dir() function is just the
filename, not the path, so that's all you'd have in strFileName. Your
MsgBox must have shown you that. Modify your code like this:

'----- start of revised code -----
Private Sub Command47_Click()

Const conImportFolder = _
"C:\Documents and Settings\user1\Desktop\Temp Files\ImportTesting\"

Dim strFileName As String

strFileName = Dir(conImportFolder & "*.txt")

Do While Len(strFileName) > 0

If MsgBox( _
"Import file '" & strFileName & "'?", _
vbQuestion+vbYesNo, _
"Import File?") _
= vbYes _
Then
DoCmd.TransferText acImportDelim, , _
"TableOne", conImportFolder & strFileName, _
False
End If

' Get next file to import, if any.
strFileName = Dir()

Loop

MsgBox "Done"

End Sub

'----- end of revised code -----

That's "air code", so I may have made a few mistakes. Also, the long string
literal for conImportFolder may have been wrapped onto two lines by the
newsreader, but should all be on one line.

Note that I've set it up so that it loops through all the .txt files in the
folder, offering to import each one.