strTempTable = "temp
(canBdeleted
).csv"
You shouldn't use special characters (or spaces) in names.
This would be better:
Code:
strTempTable = "temp_CanBeDeleted.csv"
Before I go back to modify the filename creation process, or manually edit each filename, is there some way that VBA can address this at the time of import?
After you get "mstrFileName", why don't you rename the file first?
Code:
Dim fInput
Dim fOutput
Dim fs
Dim sline As String
Dim strTempTable As String
Dim mstrFileName As String
Dim NewFileName As String
'somehow you get the file name/ no code shown
mstrFileName = "data_886.13_2012.csv"
NewFileName = Left(mstrFileName, Len(mstrFileName) - 4)
NewFileName = Replace(NewFileName, ".", "_")
NewFileName = NewFileName & ".csv"
'rename the CSV file
Name mstrFileName As NewFileName
strTempTable = "temp_CanBeDeleted.csv"
Set fs = CreateObject("Scripting.filesystemobject")
Set fInput = fs.opentextfile(NewFileName)
Set fOutput = fs.createtextfile(strTempTable)
sline = fInput.readline 'if you want to skip the first line
Do While fInput.atendofstream <> True
sline = fInput.readline
fOutput.writeline sline
Loop
' CLOSE the temp csv file before the next step - open doc prevents import
fInput.Close
fOutput.Close
If you are not using an option, just use commas, don't use empty string. At the end, just don't include anything
Code:
DoCmd.TransferText acImportDelim, , strTable, strTempTable, False