I sometimes get the error "The object invoked has disconnected from its
clients" when my macro tries to import TXT files. There seem to be many
different causes of this error posted in the forum. Any suggestions about
what is going wrong in my case?

Hint: if I actually import a file manually, then run the macro (without
changing the CurDir, although I don't know if it matters), I do not get the
error. If I close all open workbooks, the error comes back, even though the
macro creates a new workbook before attempting the import.

The relevant code is:

Workbooks.Add
iReport = 0
vFile = Dir("*.TXT")
If vFile = "" Then
Call MsgBox("No TXT files found in folder " & CurDir, vbExclamation, _
"Grab_Reports()")
Exit Sub
End If

While vFile < ""
iReport = iReport + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & vFile, _
Destination:=Range("A1"))
.Name = "Report" & str(iReport)
.FieldNames = True
.RowNumbers = False ' might want 'True' for
Access export
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = nHeaderRows + 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 4, 2, 1, 1,
1, 1)
.TextFileFixedColumnWidths = Array(5, 4, 17, 21, 4, 4, 24, 16, 8, 8,
4, 7, 7)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

vFile = Dir ' Next report ...
Sheets.Add
Wend

Thanks for any ideas!

Hi
You could sprinkle your code with a few DoEvents (read the help on
issues with it). e.g at the end of each loop. It just means that one
loop is fully processed before the next kicks in....I think.
regards
Paul

Allen_N wrote:

I sometimes get the error "The object invoked has disconnected from its
clients" when my macro tries to import TXT files. There seem to be many
different causes of this error posted in the forum. Any suggestions about
what is going wrong in my case?

Hint: if I actually import a file manually, then run the macro (without
changing the CurDir, although I don't know if it matters), I do not get the
error. If I close all open workbooks, the error comes back, even though the
macro creates a new workbook before attempting the import.

The relevant code is:

Workbooks.Add
iReport = 0
vFile = Dir("*.TXT")
If vFile = "" Then
Call MsgBox("No TXT files found in folder " & CurDir, vbExclamation, _
"Grab_Reports()")
Exit Sub
End If

While vFile < ""
iReport = iReport + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & vFile, _
Destination:=Range("A1"))
.Name = "Report" & str(iReport)
.FieldNames = True
.RowNumbers = False ' might want 'True' for
Access export
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = nHeaderRows + 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 4, 2, 1, 1,
1, 1)
.TextFileFixedColumnWidths = Array(5, 4, 17, 21, 4, 4, 24, 16, 8, 8,
4, 7, 7)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

vFile = Dir ' Next report ...
Sheets.Add
Wend

Thanks for any ideas!