Access VBA Import Text Module

[cms370]

All,

I have limited experience with VBA but have used it in the past.

I am currently trying to set up a module within access so that I can read the current files of the directory (.txt files) and then bring them into a database using "TransferText". I am running into one issue when using this command.

------------------------This code works:------------------------
Sub Test()

Dim file_name As String
Dim strPath As String
strPath = CurDir & "\" & "*.txt"
strFile = Dir(strPath)
'file_name = CurDir & "\" & strFile
file_name = "C:\Access_Database_Process\POLL_Output_2010_06_08_14_09_16.txt"
DoCmd.TransferText acImportDelim, "Import_ Specification", "NC_Haul_Opt_Table", file_name

End Sub
---------------------------------------------------------------------


------------------------This code doesnt work:------------------------
Sub Test()
Dim file_name As String
Dim strPath As String
strPath = CurDir & "\" & "*.txt"
strFile = Dir(strPath)
file_name = CurDir & "\" & strFile
'file_name = "C:\Access_Database_Process\POLL_Output_2010_06_08_14_09_16.txt"
DoCmd.TransferText acImportDelim, "Import_ Specification", "NC_Haul_Opt_Table", file_name
End Sub
---------------------------------------------------------------------

I have verified that the file name with path is being generated correctly for the code "that doesnt work" but I still receive an error stating "run time error 31519: you can not import this file"

What am I missing here?

Thanks.

 

[vbaInet]

I strongly feel that your file_name isn't producing the same string as the one that is working. Where did you declare CurDir by the way?

Another I would question is why you're searching whether A TEXT FILE exists? Shouldn't you be checking whether a SPECIFIC FILE exists?

NB: To get the path of your database use CurrentProject.Path. So:
file_name = CurrentProject.Path & "\" & strFile

Welcome to AWF by the way

 

[boblarson]

You can't import a file based on a *

You need a file name. If you want to get the file name from in there then you will have to iterate through that directory and get the file name. But you can't just use a wildcard.

 

[gemma-the-husky]

i dont think thats right, bob

you can do widcards within dir

fname = dir(C:\*.txt")

it returns the first file in folder c:\ matching the spec *.txt

I think there must be something wrong with curdir.

 

[boblarson]

i dont think thats right, bob

you can do widcards within dir

fname = dir(C:\*.txt")

it returns the first file in folder c:\ matching the spec *.txt

I think there must be something wrong with curdir.

I see that is correct (after testing).

So, this should work:


file_name = Dir(CurDir & "\*.txt")

The rest would be extraneous and the only way this wouldn't work is if there is no text file in that directory, correct Dave?

 

[vbaInet]

Yeap, that was what I was getting at.

Oops.. answered the question meant for Dave

 

[boblarson]

Ah, but no. The Curdir may not be set to what they think it is. They would need to set it using ChDir to get it there. If the OP wants the current location of the Access database they would be better off using other code.

 

[boblarson]

Although using Dir("C:\Temp\*.txt") would only give you the FIRST one wouldn't it? You would need to delete that file in order for you to run it again to give you the other file unless you used the file system object to iterate through the file folder (or am I off on that one too?).

 

[vbaInet]

Precisely, FSO in this case for what you described. But I'm thinking that the OP has dedicated the folder in which his app resides for outputting and there would ever be only one text file in there. We can only assume.

 

[boblarson]

Precisely, FSO in this case for what you described. But I'm thinking that the OP has dedicated the folder in which his app resides for outputting and there would ever be only one text file in there. We can only assume.

Well, according to their very first post:

so that I can read the current files of the directory

(emphasis added by me)

 

[vbaInet]

Ooh... missed that bit Bob.

 

[ByteMyzer]

You might try something like the following:

[COLOR="Navy"]Sub[/COLOR] Test()

    [COLOR="navy"]Dim[/COLOR] file_name [COLOR="navy"]As String
    Dim[/COLOR] strPath As String

    [COLOR="DarkGreen"]' NOTE: Make certain that CurDir is set to the
    '       directory of your choice[/COLOR]
    
    strPath = CurDir & "\" & "*.txt"
    strfile = Dir(strPath)

    [COLOR="darkgreen"]' Loop through the files[/COLOR]
    [COLOR="navy"]Do While[/COLOR] strfile > ""
        file_name = CurDir & "\" & strfile
        DoCmd.TransferText acImportDelim, _
            "Import_ Specification", _
            "NC_Haul_Opt_Table", _
            file_name
        [COLOR="darkgreen"]' Get the next file in the Directory[/COLOR]
        strfile = Dir
    [COLOR="navy"]Loop

End Sub[/COLOR]

 

[cms370]

Ok...looks like you all have been very busy...appreciate the discussion.

I took vbaInet's suggestion and used CurrentProject.Path and now things are working great. I must have been doing something wrong with the CurDir as outlined above. Anyways, things are working great with CurrentProject.Path.

Btw...the only files that reside in this directory are the ".txt" files that are to be processed. All that is needed is to search for these files and then read them in with the vba module.

Thanks to everyone for the help and discussion.

 

[boblarson]

Ok...looks like you all have been very busy...appreciate the discussion.

I took vbaInet's suggestion and used CurrentProject.Path and now things are working great. I must have been doing something wrong with the CurDir as outlined above.

Yes, you were. You were assuming that it has a certain value, and you have to hearken back to DOS to realize that you have to use ChDir to change the Curdir to the path you want. You would need to set Curdir to CurrentProject.Path if you wanted to guarantee that it was set to the location of the database. Otherwise, it is likely set to something else based on some other program or Windows did.