flyfisher15
07-12-2012, 07:51 AM
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
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