Solved: Importing a .txt file without a user prompt

[flyfisher15]

I'm trying to set up a script that will import a .txt document without user intervention. I've gotten it to find the exact file that it should be

C:\Documents and Settings\*****\Desktop\SRE Script\FDR\7.2.2012 11.15.18 AM FDR

When I run the script FinfsFile = C:\Documents and Settings\*****\Desktop\SRE Script\FDR7.2.2012 11.15.18 AM FDR.txt

But when I try to refresh the file to complete the import, the it tells me that the .txt file cannot be found.

Here's what I have.

Dim datetest As String
Dim FinDateTest As String
Dim fs As String
Dim fsFile As String
Dim i As Long
Dim LfsFile As String
Dim FinfsFile As String

Workbooks("Automated SRE Dashboard").Activate

x = 2

'finds date needed in workbook

While Range("C" & x) <> ""
x = x + 1
Wend

If Range("C" & x - 1) = "Date Sent" Then
datetest = Range("A4")
Else
datetest = Range("C" & x)
End If

FinDateTest = StripIllegalChar(datetest)


Set newbook = Workbooks.Add
With newbook
.Title = "xferWB"
.Subject = "xferWB"
.SaveAs Filename:="xferWB.xls"
End With
Workbooks("xferWB").Activate

'searches files for correct date

fs = "C:\Documents and Settings\*****\Desktop\SRE Script\FDR"

If Dir(fs, vbDirectory) <> "" Then

fsFile = Dir(fs & "\*.txt")
Do While fsFile <> ""

LfsFile = Left(fsFile, 8)

If LfsFile = FinDateTest Then

FinfsFile = Trim(fs & fsFile)
Range("a1") = FinfsFile

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FinfsFile _
, Destination:=Range("$A$1"))
.Name = LfsFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
fsFile = Dir
Loop
End If

[Kenneth Hobs]

Why would you need a refresh?

See if my QueryTable routine helps any. http://www.vbaexpress.com/forum/showthread.php?t=42676

[flyfisher15]

The querytable that I'm trying to run looks pretty much the same as yours. Any idea what could be wrong?

[Kenneth Hobs]

If you use debug.print, debug by F8 and such, you can see that the problem is here:
FinfsFile = Trim(fs & fsFile)

which should be:
FinfsFile =fs & "\" & fsFile though I usually just add the trailing backslash to fs to begin with. There is an API routine that I could show you that would add it if needed though it is easily coded for otherwise.

[flyfisher15]

you sir are the man! IT WORKED! I poured over this all day yesterday trying to get it to load.