One way around it:
Sub ToManyPrices()
Application.Goto Reference:="Dates2"
Collectdat = Range("Dates2").Value
If Range("f8") < "" Then
BeYondDate = Range("f7", Range("f7").End(xlDown))
' this gets the list of files to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If
On Error GoTo Nofile
For i = 1 To UBound(BeYondDate)
Workbooks.OpenText Filename:="I:\My Documents\sharescope export\" & _
BeYondDate(i, 1) & ".prn", _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, _
TextQualifier:= xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9), _
Array(4, 9), Array(5, 9), Array(6, 1), _
Array(7, 9)), _
TrailingMinusNumbers:=True
Name = Range("a1") 'epic
Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates
Workbooks(BeYondDate(i, 1) & ".prn").Close False
getNextFile:
Next i
On Error Resume Next
'more code???
Exit Sub
Nofile:
MsgBox ("File " & "'" & BeYondDate(i, 1) & "'" & " not found in Sharescope
Export ")
Resume getNextFile:
End Sub
===
but you could get rid of the error checking and just check the for the existence
of each of the files:
Sub ToManyPrices()
Dim myFileName As String
'Application.Goto Reference:="Dates2"
'Collectdat = Range("Dates2").Value
If Range("f8") < "" Then
beYonddate = Range("f7", Range("f7").End(xlDown))
' this gets the list of files to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If
For i = 1 To UBound(beYonddate)
myFileName = "I:\My Documents\sharescope export\" & _
beYonddate(i, 1) & ".prn"
If Dir(myFileName) = "" Then
MsgBox "File " & "'" & beYonddate(i, 1) & "'" & _
" not found in Sharescope Export "
Else
Workbooks.OpenText Filename:=myFileName, _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 9), _
Array(4, 9), Array(5, 9), Array(6, 1), _
Array(7, 9)), _
TrailingMinusNumbers:=True
Name = Range("a1") 'epic
Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates
Workbooks(beYonddate(i, 1) & ".prn").Close False
End If
Next i
End Sub
And this line in either procedure looks funny to me:
Name = Range("a1") 'epic
Does name belong to something: activesheet.name????
or is it a variable.
If it's a variable, I'd change it to something that isn't builtin (myName???).
RobcPettit wrote:
Sub ToManyPrices()
I can not seem to get this Error trap to work, when I find the first file that
is not in the directory my msgbox works, when I find a 2nd file that is not in
the dir the program crashes, could you advise why.
Application.Goto Reference:="Dates2"
Collectdat = Range("Dates2").Value
If Range("f8") < "" Then
BeYondDate = Range("f7", Range("f7").End(xlDown)) ' this gets the list of files
to look for
Range("f8", Range("f8").End(xlDown)).Clear
Else
Exit Sub
End If
For i =1 To UBound(BeYondDate)
On Error GoTo Nofile
Workbooks.OpenText Filename:="I:\My Documents\sharescope export\" &
BeYondDate(i, 1) & ".prn", _
Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
_
Array(2, 1), Array(3, 9), Array(4, 9), Array(5, 9), Array(6, 1),
Array(7, 9)), _
TrailingMinusNumbers:=True
Name = Range("a1") 'epic
Importdat = Range("b1", Range("c1").End(xlDown)) 'shares dates
Workbooks(BeYondDate(i, 1) & ".prn").Close False 'closes share file
comparedat
Nofile:
MsgBox ("File " & "'" & BeYondDate(i, 1) & "'" & " not found in Sharescope
Export")
Next i
End Sub
--
Dave Peterson
