Setting a Path in TransferText

[BamaColtsFan]

Team,

I'm trying to use the TransferText function I found listed here by several users. I have set up my own version and it woirks very nicely except that I now need to call a stored value to get the file path for the function. My first example shows the function in it's working form based on what I found here:

Sub transferHardPath()
Dim path As String
path = "C:\Files\Test_Data_One.txt"
DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTableTestOne", path
End Sub

Now, I'd like to be able to select the path value from a stored table in my DB. The value in this example would be "C:\Files\" and is stored in the table Set_Path. As posted below I get "Compile Error: Type mismatch." If I wrap newPath in quotes I get an error that says Access can't find the object.

Sub transferSoftPath()
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTableTestTwo", newPath & "Test_Data_Two.txt"
End Sub

So, my question is what am I doing wrong in the second example? Any advice would be appreciated!

THNX

 

[SOS]

Yep, you aren't referring to the right thing. You need to refer to a FIELD value from your recordset:

Sub transferSoftPath()
Dim db As DAO.Database
Dim newPath As DAO.Recordset
   Set db = CurrentDb()
   Set newPath = db.OpenRecordset("Set_Path")

   DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTableTestTwo", newPath[B][COLOR=red]![/COLOR][COLOR=red]PathFieldNameHere[/COLOR][/B] & "[COLOR=red][B]\[/B][/COLOR]Test_Data_Two.txt"
End Sub

And I would not try concatenating there, but use:

Sub transferSoftPath()
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim strPath As String

   Set db = CurrentDb()
   Set newPath = db.OpenRecordset("Set_Path")
[B][COLOR=red]strPath = newPath!PathFieldNameHere & "\Test_Data_Two.txt"[/COLOR][/B]
   DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTableTestTwo", [COLOR=red][B]strPath[/B][/COLOR]
End Sub

I assumed you hadn't had the backslash stored but if you do, omit it from the second part.

 

[BamaColtsFan]

Excellent! That worked like a charm, as always!

 

[SOS]

Excellent! That worked like a charm, as always!

Glad to have been of some help.

 

[BamaColtsFan]

There is one other thing you might be able to push me in the right direciton on... If I wanted to have this function accept a variable of the file name rather than hard coding the file name, where should I start looking? What I think I want to do is call this function in a macro, supply the variable file name and let the function import the file. By the way... I did convert it to a public function after you got it working... Here is the revised code:

Public Function SoftPathUtility()
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim strPath As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
strPath = newPath!path & "\Test_Data_Two.txt"
DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTableTestTwo", strPath
End Function

Again, thanks a million for your help!

 

[gemma-the-husky]

do you mean this bit

"\Test_Data_Two.txt"

the normal way is to use a fielopensave dialog of some sort, navigate to the fiel you want and use that.

the fieldialog will return a string representing the full path to the selected file

sort of

startfolder = "C:\"
myfile = getfiledialog(startfolder)

with most of them you can specify the file pattern you can see
eg *.* or *.csv etc

 

[BamaColtsFan]

That is the part I want to pass as a variable. When this runs there are at least 9 files that get loaded. I can repeat this code 9 times and get everything but I'm thinking in terms of simplification thus the idea of passing to file name as a variable. I really don't want the user to select the file each time (and certainly not 9 times for every load). The file names are always the same so it seems logical to me to have a reusable function to do the task. In thinking about it, I really could use a function that uses 3 seperate variables. First, the file name, but I could also pass the specification as a variable as well as the path. I have a few other files that need to be loaded into this database but not as frequently as the 9 previously noted. With the 3 variables, this function could be used for all file loads in the application as opposed to coding each one individually.