I'm working on a project where I'm opening a .CSV file using QueryTables.Add. 
My macro works except that I've discovered a possible case where the .CSV 
file is empty. When the macro hits the empty file, it raises an error "Method 
'refresh' of object 'QueryTable' failed", as might be expected. 

When another .CSV file containing data is opened after the error above has 
occurred, QueryTables raises the error "Application-defined or object-defined 
error,", even though there is data. The only way I've found to reset 
QueryTables is to close all open instances of Excel and restart. 

I'm using Excel 2003 for this project. I 've found that Excel 2007 does not 
exhibit this behavior. It's able to recover from the empty-file error and 
resume. 

QUESTION: How can the macro error handler reset QueryTables Text errors? 

WORKAROUND: Check file length > 0 before attempting to connect with 
QueryTables.Add. Solves the problem but I'd like to know how to reset the 
error if it does occur.

A simplified sub that demonstrates the problem appears below. To run the test:

1. Open the macro editor for Sheet1 and paste in the code. Replace the 
assignment for "Filename" with a filepath/filename for a suitable non-empty 
file and run the macro. It should open the file and display data in a new 
worksheet "test". 

2. Open the macro editor for Sheet2 and paste in a duplicate of the code. 
Substitute the filename for an empty file for "Filename", delete the new 
worksheet "test", and re-run the macro. An error message appears.

3. Delete the new worksheet "test" and re-run the macro in Sheet1. The error 
message appears again. The only way I've found to get the macro in Sheet1 to 
execute again is to restart Excel.

Sub testQuery()
On Error GoTo errorHandler
Dim newSheet As Worksheet
Dim Filename As String
Filename = "file.CSV"

Set newSheet = Sheets.Add
	newSheet.Name = "test"

	With newSheet.QueryTables.Add(Connection:="TEXT;" & Filename, _
		Destination:=newSheet.Range("A1"))
		.Name = "test"
		.FieldNames = False
		.RowNumbers = False
		.FillAdjacentFormulas = False
		.PreserveFormatting = True
		.RefreshStyle = xlInsertDeleteCells
		.SavePassword = False
		.SaveData = True
		.AdjustColumnWidth = True
		.RefreshPeriod = 0
		.TextFilePromptOnRefresh = False
		.TextFileStartRow = 1
		.TextFileParseType = xlDelimited
		.TextFileTextQualifier = xlTextQualifierDoubleQuote
		.TextFileConsecutiveDelimiter = False
		.TextFileTabDelimiter = False
		.TextFileSemicolonDelimiter = False
		.TextFileCommaDelimiter = True
		.TextFileSpaceDelimiter = False
		.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1)
		.Refresh BackgroundQuery:=False 
	End With
	Exit Sub
	If newSheet.Range("A1").Value2 = "" Then MsgBox ("Cell is blank")
	newSheet.Delete

errorHandler:
	MsgBox prompt:=Err.Description, Title:=Err.Number
	Exit Sub
End Sub

I added a test for file size in the code below


Sub testQuery()

Set fs = CreateObject("Scripting.FileSystemObject")

On Error GoTo errorHandler
Dim newSheet As Worksheet
Dim Filename As String
Filename = "file.CSV"
Set f = fs.getfile(Filename)
If f.Size = 0 Then Exit Sub


Set newSheet = Sheets.Add
newSheet.Name = "test"

With newSheet.QueryTables.Add(Connection:="TEXT;" & Filename, _
Destination:=newSheet.Range("A1"))
.Name = "test"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:=False
End With
Exit Sub
If newSheet.Range("A1").Value2 = "" Then MsgBox ("Cell is blank")
newSheet.Delete

errorHandler:
MsgBox prompt:=Err.Description, Title:=Err.Number
Exit Sub
End Sub


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=160662

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]