R
RK Henry
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
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