A couple of things:
You have spaces in your table names. Best thing would be to remove the spaces or replace spaces with underscores. Should NEVER use spaces in object names.
The next best would be to enclose the table names with brackets:
Code:
vTbl = "H1PD XRAY Data"
would become
Code:
vTbl = "[H1PD XRAY Data]"
In the sub "Public Sub Import1TextFile(ByVal pvFile)", there are variables not declared: "vSpecFile" and "vTbl"
<snip>I would like to
import the files</snip>
The main thing is that the Transfertext command has the wrong parameter
Code:
DoCmd.TransferText acExportDelim, vSpecFile, vTbl, vTarg, True
To Import data, this should be:
Code:
DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True
Here is the sub with my changes: (my changes in blue)
Code:
'the source file is copied to a generic file everytime
'----------------
Public Sub Import1TextFile(ByVal pvFile)
'----------------
Dim vDir As String
Dim vTarg As String
Dim vSpecFile As String
Dim vTbl As String
'make a copy
vDir = "C:\REDSIII\ImportedFiles\"
MakeDir vDir
vTarg = vDir & "File2Import.txt"
'import data
FileCopy pvFile, vTarg
Select Case True
Case InStr(pvFile, "FW_H1PD_OE-STRP") > 0
vSpecFile = "H1PD-XRaySpec"
vTbl = "[H1PD XRAY Data]"
Case InStr(pvFile, "FW_IMGC_RS-STRP") > 0
vSpecFile = "IMGC-ImageSpec"
vTbl = "[IMGC IMAGE Data]"
Case InStr(pvFile, "FW_ITXM_RS-STRP") > 0
vSpecFile = "ITxM-BloodSpec"
vTbl = "[ITXM BLOOD Data]"
Case Else
Exit Sub
End Select
' DoCmd.TransferText acExportDelim, vSpecFile, vTbl, vTarg, True
DoCmd.TransferText acImportDelim, vSpecFile, vTbl, vTarg, True
End Sub
Oh, I keep getting 'The test file specification 'H1PD-XRaySpec' does not exist. You cannot import, export, oir link using the specification.
Did you create an import file spec named 'H1PD-XRaySpec'?
(BTW, shouldn't use special characters in object names. Only letters, numbers and the underscore)