Opening TXT too slow

C

carloshernandezy

Opening a *.txt with a variable name is to slow, the name has varible
string like DEAE****.txt where the **** is a number between 0000 and
9999. There is only one archive with this variable name in the DIR. I
use this code but it is too slowly, what can I do.
__________________________________________________________________
For i = 1 To 9999

On Error Resume Next
Workbooks.OpenText Filename:="DEAE" & i & ".txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1))

Next i
__________________________________________________________________

Thanks to all
 
H

Harlan Grove

[email protected] wrote...
start using a DATABASE instead of excel
Click to expand...

Given the OP was opening text files with VBA, how would either the code
OR the execution time be ANY different using VBA from Access vs using
VBA from Excel?

Someday you're gonna hafta pull your head out.
 
D

Dave Peterson

You do this loop 1000 times--no matter if you open it the first time through or
not...

You could still loop through the 1000 numbers and exit after you've done the
import...

for i = 1 to 9999
on error resume next
workbooks.open(.....)
if err.number <> 0 then
err.clear
'but stay in the loop
else
'but if there wasn't an error, you found it
exit for
end if
next i

But I think it would be better to just look for a file named DEAE????.Txt. But
I'm confused about your post and your code.

It looks like you describe the file as having 4 numeric characters
(DEAE****.txt), but I may be reading too much into that. But your code looks
for files named DEAE1.txt, DEAE2.txt.

I'm gonna assume your code is the way you want it.

Option Explicit
Sub testme()

Dim myPath As String
Dim myFile As String
Dim FoundOne As Boolean

myPath = "C:\my documents\excel\test\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "DEAE*.txt")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

FoundOne = False
Do While myFile <> ""
If IsNumeric(Mid(myFile, 5, Len(myFile) - 8)) Then
'found one that's numeric
FoundOne = True
Exit Do
End If
myFile = Dir()
Loop

If FoundOne = False Then
MsgBox "no files found"
Else
Workbooks.OpenText Filename:=myPath & myFile, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, _
Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8, 1))
End If
End Sub
 
C

carloshernandezy

Thanks Dave, I think the code was too slow because I was triying to
open 10000 documents, I have change my code to this one here it only
tries to open the document if varArchivo exist.

But reading your coments I notice that if the document have a name like
DEAE0010.txt my code will miss this document and fail. Have you got any
idea to change this?.

Thank you
----------------------------------------------------------------------------------
For i = 1 To 9999
On Error Resume Next

varArchivo = Dir("DEAE" & i & ".txt")

If varArchivo <> "" Then

Workbooks.OpenText Filename:=varArchivo, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1))


End If
Next i

__________________________________________________________________________________
 
D

Dave Peterson

You could use:

dir("deae" & format(i,"0000") & ".txt")

to make sure the numbers are always treated as 4 digits.

===
You may want to review the other suggestion--it shows a way to just look once.
 
C

carloshernandezy

Thanks Dave for your help, I finally took your suggestion.

One more cuestion, it´s possible to close the *.txt without saving any
changes, after copying some data. I have problems with
"ThisWorkbook.Close savechanges:=False" because it is not already a
workbook. Can I change this for a txt file?


Thanks
 
D

Dave Peterson

And since you're working with a text file, it' will never contain code. That
means that Thisworkbook would never refer to the text file.

But you can do this:

dim wkbk as workbook
'''a bunch of code

Workbooks.OpenText Filename:=varArchivo, Origin:= ....
'right after the .opentext line
set wkbk = activeworkbook

'''another bunch of code

wkbk.close savechanges:=false
 
Top