Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset QueryTable error on empty text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset QueryTable error on empty text file
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/sh...d.php?t=160662 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported text file to begin from the first empty cell | Excel Programming | |||
Reset Delimiter when opening text file | Excel Programming | |||
Saving to text file - tabs from empty cells ignored | Excel Programming | |||
how to reset data entry cells to 0 or empty | Excel Programming | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming |