import text with docmd.transfertext

[genny]

I am trying to import a text file using the command docmd.transfertext
this is my code:

DoCmd.TransferText acImportDelim, "IMPORT", _
"AMEX", "volbyclass-amex.txt", -1

my text file is in the same directory as my access database but it doesn't work. it gives me an error that it cannot find the file.

If I put the whole path (ex: c:\...) it works perfectly but I obviously don't want to do that because it won't be portable. what am I missing ?

 

[HGMonaro]

it might depend on what version of access you're using but in '97 you need to build up the path in a variable. Code to do it is in here someplace but here it is as I use it regularly (happen to have a db with it in it open!). CurrentDB().Name gives you the full path of the DB and you can work it out from that.

Function getPath()

Dim DB_Path As Variant

Dim strFullPath As String
Dim i As Integer

' get the name of the DB which includes the path
strFullPath = CurrentDb().Name

' loop around testing to see where \ occurs then use the left section up to that character
For i = Len(strFullPath) To 1 Step -1
    If Mid(strFullPath, i, 1) = "\" Then
       DB_Path = Left(strFullPath, i)
    Exit For
    End If
Next

getPath = DB_Path

End Function

here's it in use.

Function Load_MYOB_Data()

Dim ImportFile As Variant
Dim DB_Path As Variant

' get the DB path
DB_Path = getPath()

' create field with path and MYOB filename to import
ImportFile = DB_Path + "MYOB\myob.xls"

' Import file into table
DoCmd.TransferSpreadsheet acImport, , "MYOB Import", ImportFile, True


End Function

 

[genny]

thanks a lot . It did not know of CurrentDB().Name for the path. it works perfectly.

 

[boblarson]

And in 2000 and above you can simply use

CurrentProject.Path

to get the path where the database lies.

 

[boblarson]

So for Access 2000 and above you can simply go with

Dim strImportPath As String

' create field with path and MYOB filename to import
strImportPath = CurrentProject.Path & "\MYOB\myob.xls"

' Import file into table
DoCmd.TransferSpreadsheet acImport, , "MYOB Import", strImportPath, True

And HGMonaro you shouldn't use VARIANTS unless they are necessary. Since you will not be assigning a NULL value to it you should assign it the actual type.