Edit filepath on batch job

[ziglex]

Hi all,
I'm currently looking to change the ending of all filepaths of imported files whenever I run the import. I want to change the path ending from .txt to .doc. So basically, the link/path to the file should be got, the ending changed from .txt to .doc and then the path (with the .doc ending) put into the table.

I'm unfortunately struggling to use the REPLACE function, which doesn't seem to work how I hoped. See the code below, any help would be appreciated.

Private Sub bimportinternal_Click()
On Error Resume Next

strFolderPath = "S:\Foo reports\Searchable\"
strFolderPathSave = "S:\Foo reports\Searchable\Archiveword\" & objF1.Name
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

Set db = CurrentDb
Set rs = db.OpenRecordset("fooreport", dbOpenDynaset)
For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
    DoCmd.TransferText acImportDelim, "internalimporter", "fooreport", strFolderPath & objF1.Name, False
    db.Execute "REPLACE (strFolderPathSave,'txt','doc')"
    db.Execute "UPDATE fooreport SET linktodoc = '" & strFolderPathSave & objF1.Name & "' WHERE linktodoc Is Null"
    db.Execute "UPDATE fooreport SET reporttype = '" & "Internal" & "' WHERE reporttype Is Null"
    Name strFolderPath & objF1.Name As "S:\Foo reports\Searchable\Archivetxt\" & objF1.Name 
End If
Next

 

[Ranman256]

use REPLACE in a query
update

set [field1] = Replace([field1],".xls",".doc");

then just run the query docmd.openquery quReplaceQry

 

[ziglex]

use REPLACE in a query
update

set [field1] = Replace([field1],".xls",".doc");

then just run the query docmd.openquery quReplaceQry

Thank you for that, I will try that tomorrow. Does anyone have an option with SQL? I'd like to be able to keep it all in SQL if at all possible as I'll be handing the code over to others and don't really want to have it all in different places.

 

[vbaInet]

This:

db.Execute "REPLACE (strFolderPathSave,'txt','doc')"

... doesn't exist.

What Ranman256 showed you is the way to replace text in your tables through SQL.

 

[ziglex]

Thanks for this - works perfectly.