Hi, I need some assistance...I created a command button for the user to press and import a file. This works great but then I had to involve IT in my company and I am getting is headache... IT function is to everyday automate the process by taking the file from one system and dumping it into the shared drive. The file has no associated program and unless I resave and change it myself. So, I looked some options and I put the code below but it tells me that "File Not Found". The file name is "RIMARY D" and in my "H" drive. What am I missing??? any assistance will be greatle appreciated
Public Function ImportNonTXT()
Dim fs, Fn, FLength, Fext, FDot, FOrig
Set fs = CreateObject("Scripting.FileSystemObject")
Set Fn = fs.Getfile("H:\RIMARY D") - where it gets stuck
' This holds the file's original name for rename later.
FOrig = "RIMARY D"
' Get the length of the file name.
FLength = 8
' Set this value = to the last four characters of the file name.
Fext = Right("RY D", 4)
' Set this = to the first character.
FDot = Left("R", 1)
' If there is a dot in the fourth from the last position...
If FDot = "." Then
'... and the extension is not .txt.
If Fext <> ".txt" Then
' Remove the extension from the file name.
Fn.Name = Left("RIMARY D", (FLength - 4))
' Add the .txt to the file name.
Fn.Name = "RIMARY D" & ".txt"
End If
Else
' If there is not a dot in the fourth position
' add the .txt extension.
Fn.Name = "RIMARY D" & ".txt"
End If
' Transfer the file to a new table.
DoCmd.TransferText acImportDelim, "", "Tbl_Text", "H:\Rimary D.txt", False, ""
' After the file is transferred, rename it back to
' its original name.
Fn.Name = FOrig
End Function
Public Function ImportNonTXT()
Dim fs, Fn, FLength, Fext, FDot, FOrig
Set fs = CreateObject("Scripting.FileSystemObject")
Set Fn = fs.Getfile("H:\RIMARY D") - where it gets stuck
' This holds the file's original name for rename later.
FOrig = "RIMARY D"
' Get the length of the file name.
FLength = 8
' Set this value = to the last four characters of the file name.
Fext = Right("RY D", 4)
' Set this = to the first character.
FDot = Left("R", 1)
' If there is a dot in the fourth from the last position...
If FDot = "." Then
'... and the extension is not .txt.
If Fext <> ".txt" Then
' Remove the extension from the file name.
Fn.Name = Left("RIMARY D", (FLength - 4))
' Add the .txt to the file name.
Fn.Name = "RIMARY D" & ".txt"
End If
Else
' If there is not a dot in the fourth position
' add the .txt extension.
Fn.Name = "RIMARY D" & ".txt"
End If
' Transfer the file to a new table.
DoCmd.TransferText acImportDelim, "", "Tbl_Text", "H:\Rimary D.txt", False, ""
' After the file is transferred, rename it back to
' its original name.
Fn.Name = FOrig
End Function
