Run-time Error '-2147417848 (80010108)'

[mrsviqt]

I have a macro that is doing a number of things, starting with importing a
txt file. When I try to run it, I get this error: "Run-time Error
'-2147417848 (80010108)' Automation Error The object invoked has
disconnected from its clients.

When I Debug. it goes to the following line within the code ".Refresh
BackgroundQuery:=False" I have tried everything I know to fix it (granted,
that is not very much). Below is the code.

Any help would be appreciated. Thanks in advance.

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Data\SALES.TXT", _
Destination:=Range("A1"))
.Name = "SALES"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = -536
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(26, 14, 7, 11, 1, 17, 17, 8, 3)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[FSt1]

hi
i think it's because you have the refreshperiod set to zero. this is the
amout of time in minutes allowed between refreshes. in your case you create
the query which brings the data in then you want to refresh the data
immediately again and with refreshperiod set to zero, i don't think you can
do that.

but more puzzling is why are you creating a new query each time. i see the
workbook add line but this would create a new query in each workbook????
once created, excel remembers the query, assigns in a named range name and
remembers all of the query properties. after that all you need to do is
refresh the query and one line of code would do it.

range("A1").querytable.refresh backgroundquery:=false

I never recomend a background query. has the potential to screw other things
up.

if you are wanting to save a copy of the query run, just save the data which
would not be refreshable instead of creating a new, refreshable query. you
would use less disk space that way.

regards
FSt1