QueryTable function fails intermittently

[PJ_in_FL]

Running Excel 2007 on XP Sp3.

I have a routine that adds data from a .TXT file into a sheet containing a database. I run the routine after getting the name of the file in a Userform and saving it into a document property. The routine below runs without errors, but sometimes the data doesn't get put in the sheet. When I step through the routine, sometimes it executes the .Refresh line and still the data is not added. Then when I rerun the same routine (push the pointer back up to this routine from the calling routine and step through with F8 again) with no other changes the data is added as expected.

If it's relevant, the sheet "Data" may contain over 100K records, and each .TXT file can contain from 500 to 10K records to add to the sheet.

I'm hoping more eyes on this routine can locate what the problem is, because I sure can't see it!

Named range Database is defined as "=Data!$A$1"
Document property "_LastTextFile" is set to the complete file name, e.g. "F:\Data_capture_2011-03-25_16.31.05.txt"

Code: Select all

Sub GetDataFile()
Dim vFile As Variant
Dim lRow As Long
Dim rngNew As Range


    lRow = Sheets("Data").Range("Database").End(xlDown).Row + 1
    vFile = "TEXT;" & GetPropString("_LastTextFile")
    Set rngNew = Range("$A$" & lRow)

    With ActiveSheet.QueryTables.Add _
        ( _
        Connection:=vFile, _
        Destination:=rngNew _
        )
        .Name = "Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With 

End Sub

The calling routine is simply:

Code: Select all

Private Sub cbOK_Click()
    Unload frmImport
    Application.ScreenUpdating = False
    
    GetDataFile       ' Import new file into database
    Clean_TS_Column   ' Correct the formatting on the timestamp column
    DeleteDuplicates  ' deletes duplicate records based on timestamp and values in B and C columns
    SortData          ' resorts all data based on timestamp
    
    Application.Calculate
    Application.ScreenUpdating = True

End Sub

Typical line (sanitized) from the .TXT file (with {TAB} added where the tab characters are)

Code: Select all

=DATEVALUE("Mar 24, 2011")+TIMEVALUE("8:36 pm"){TAB}DESCRIPTION OF THIS DATA POINT{TAB}=HYPERLINK("http://WEB_PAGE_WHERE_DATA_COLLECTED.html"){TAB}VALUE1{TAB}VALUE2{TAB}VALUE3

[HansV]

The code looks straightforward, and I don't see anything amiss... Sorry!

[PJ_in_FL]

Thanks for taking the time to look over the routine.

I did notice one thing while stepping through one more time that may be the cause of the problem. The QueryTable is being executed on ActiveSheet - which is usually the sheet "Results" where the button is located to run the import macro. Only when I manually view the "Data" sheet will the macro run. Sure enough, when I scrolled down "Results" to the row where I expected the data to be added into the "Data" sheet, there was the missing data! This has bothered me for a few weeks since I automated the data import routine! I must have been following the routine to "run > fail > check "Data" > run again > pass" steadily until I just HAD to fix it!

That said, the above suggests the solution is to change the QueryTable statement to use Sheets("Data") as is shown below.

Code: Select all

    lRow = Sheets("Data").Range("Database").End(xlDown).Row + 1
    vFile = "TEXT;" & GetPropString("_LastTextFile")
    Set rngNew = Range("$A$" & lRow)

    With Sheets("Data").QueryTables.Add _
        ( _
        Connection:=vFile, _
        Destination:=rngNew _
        )

However, that now generates the error:

Code: Select all

The destination range is not on the same worksheet that the Query table is being created on.

So that pointed to rngNew as a possible source of this error. Again, specifying the sheet as well as the range seems to correct the problem.

The new code that appears to work looks like this below, posted here should anyone else ever find this of interest:

Code: Select all

Sub GetDataFile()
Dim vFile As Variant
Dim lRow As Long
Dim rngNew As Range

    lRow = Sheets("Data").Range("Database").End(xlDown).Row + 1
    vFile = "TEXT;" & GetPropString("_LastTextFile")
    Set rngNew = Sheets("Data").Range("$A$" & lRow)

    With Sheets("Data").QueryTables.Add _
        ( _
        Connection:=vFile, _
        Destination:=rngNew _
        )
        .Name = "Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

I guess the moral of the story is "When in doubt - SPECIFY!"

[HansV]

I guess the moral of the story is "When in doubt - SPECIFY!"

That is definitely good advice!

[Jan Karel Pieterse]

Not a solution to the problem, you fixed that, but I see you are adding the querytable each time you run the code. This is not needed. In fact, adding a querytable to your sheet does not necessarily remove the existing ones (and there may be many!). After adding the QT once, it suffices to just refresh it. If set up properly (change a property of the QT), you don't even have to prompt for a filename, Excel will do that for you.
I advise you to check your file for range names, I expect there will be many similarly looking range name spointing to "old" querytables.

[PJ_in_FL]

Not a solution to the problem, you fixed that, but I see you are adding the querytable each time you run the code. This is not needed. In fact, adding a querytable to your sheet does not necessarily remove the existing ones (and there may be many!). After adding the QT once, it suffices to just refresh it. If set up properly (change a property of the QT), you don't even have to prompt for a filename, Excel will do that for you.
I advise you to check your file for range names, I expect there will be many similarly looking range name spointing to "old" querytables.

Mr. Pieterse,

You're absolutely right! I have seen many instances of "Data_n" named ranges generated. I'll go into the code to see what changes I can make to simplify the procedure as you suggested as soon as I finish copying the recoverable files from the 500GB drive that got it's $MFT file corrupted yesterday. The excel files just happened to be located on that drive. Just another Monday (when I started the copy process - 20 hours and 360,000 files and counting). <sigh>

[PJ_in_FL]

I've attempted to restructure the query as Mr. Pieterse suggested, but I've come up short. I either get errors or the query refreshes but I don't get prompted for a new text file to add to the table.

I've attached a sub with all my futile attempts, but I'm out of ideas and all the references I've found via Google haven't provided the necessary words of wisdom for my feeble attempts. I have to admit I approach Excel VBA like a blind man approaching an elephant - I can understand only a minute portion of the whole at a time - but I can usually muddle through (with a lot of Googling) to something that works. Not in this case!

Comments, hints or solutions very welcome!

Code: Select all

Sub GetLinksUpdateOnly()
Dim vFile As Variant
Dim lRow As Long
Dim rngNew As Range
Dim q As QueryTable

    lRow = Sheets("Data").Range("Database").End(xlDown).Row + 1
    vFile = "TEXT;" & GetPropString("_LastTextFile")
    Set rngNew = Sheets("Data").Range("$A$" & lRow)
    
' Original code - adds new query (and Named Range) each time a new text file is added
'------------------------------------------------------------------------------------
'    With Sheets("Data").QueryTables.Add _
'        ( _
'        Connection:=vFile, _
'        Destination:=rngNew _
'        )
'        .Name = "Data"
'        .FieldNames = True
'        .RowNumbers = False
'        .FillAdjacentFormulas = False
'        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
'        .RefreshStyle = xlOverwriteCells
'        .SavePassword = False
'        .SaveData = True
'        .AdjustColumnWidth = False
'        .RefreshPeriod = 0
'        .TextFilePromptOnRefresh = False
'        .TextFilePlatform = 437
'        .TextFileStartRow = 1
'        .TextFileParseType = xlDelimited
'        .TextFileTextQualifier = xlTextQualifierDoubleQuote
'        .TextFileConsecutiveDelimiter = False
'        .TextFileTabDelimiter = True
'        .TextFileSemicolonDelimiter = False
'        .TextFileCommaDelimiter = False
'        .TextFileSpaceDelimiter = False
'        .TextFileColumnDataTypes = Array(1, 1, 1)
'        .TextFileTrailingMinusNumbers = True
'        .Refresh BackgroundQuery:=False
'    End With


' Attempt # 1 - Run-time error "438": Object doesnt support this property or method (.Name)
'----------------------------------------------------------------
'    With Sheets("Data").QueryTables.Name("Data")
'        If .Name = "Data" Then .Refresh BackgroundQuery:=False
'    End With
    
    
    
' Attempt # 2 - Run-time error "438": Object doesnt support this property or method (.Refresh)
'----------------------------------------------------------------
'    With Sheets("Data").QueryTables.Name("Data")
'        .Refresh BackgroundQuery:=False
'    End With
    
    
    
' Attempt # 3 - Compile Error: unqualified reference (.Refresh)
'----------------------------------------------------------------
'    For Each q In Sheets("Data").QueryTables
'        .Refresh BackgroundQuery:=False
'    Next
    

    
' Attempt # 4 - runs without Excel asking for new filename to add to table
'----------------------------------------------------------------
'    Set q = Sheets("Data").QueryTables.Add _
'        ( _
'        Connection:=vFile, _
'        Destination:=rngNew _
'        )
'    q.Refresh (False)
    
    
    
' Attempt # 5 - runs without Excel asking for new filename to add to table
'----------------------------------------------------------------
'    Set q = Sheets("Data").QueryTables(1)
'    q.Refresh (False)
    
    
    
' Attempt # 6 - Run-time error "1004": Application-defined or object-defined error
'----------------------------------------------------------------
'    rngNew.QueryTable.Refresh BackgroundQuery:=False
    
    
    
' Attempt # 7 - Run-time error "1004": Application-defined or object-defined error
' This is straight from access-programmer.forum. Pure rubbish!
'----------------------------------------------------------------
    Sheets("Data").Activate
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False



End Sub

Edit: Corrected spelling of name!

[HansV]

As an alternative, you could open the text file in a new window, using Workbooks.OpenText, copy the used range below the existing data, and close the text file without saving. This won't create QueryTables since you're using a completely different method of getting at the data.

[Jan Karel Pieterse]

The correct syntax is either:

Code: Select all

    With ActiveSheet.QueryTables(1)
        .TextFilePromptOnRefresh = True
        .Refresh False
    End With

or

Code: Select all

    With ActiveSheet.ListObjects(1).QueryTables(1)
        .TextFilePromptOnRefresh = True
        .Refresh False
    End With

Whether it is the first or the second depends whether the query is a table (format as table, syntax 2) or not (syntax 1). If it is a table, the table tools tab should show up on the ribbon as soon as you select a cell within the data.

[PJ_in_FL]

Mr. Pieterse,

Thank you for the reply! I incorporated the not-a-table syntax and have gotten the file to import. Excel is one huge elephant, and I'm sure I don't have a clue to the full extent of the application.

After using the new code to import a dataset, I'm struggling with importing a second file as it appears to overlay the first. The first file (a small dataset of 220 data points) was added below the existing data (row 54132), then I imported a second file (also 220 data points), which loaded data starting at the same row, thus overwriting the first dataset.

Do I need to update the table properties to include the newly added data to the table so the next import appends rows below the existing data or set a starting point for the new data to load?

Here's the new subroutine I'm using:

Code: Select all

Sub GetLinksUpdateOnly()
    
' Attempt # 8 - Jans post, not a table option, sheet Results selected - imports data, overlays previous data!!!
'----------------------------------------------------------------
    With Sheets("Data").QueryTables(1)
        .TextFilePromptOnRefresh = True
        .Refresh False
    End With

End Sub

[PJ_in_FL]

Update: Actually I examined the results of the import more closely and found a serious problem. My data table extends down to row 73410. After importing the first file, the data imports to the row defined by the DATA named range, and clears all other data in that range. It so happened an error of mine set the range from rows from 54123 to 62142, so all the data in that range is lost.

It appears I need to update the DATA range to the import location. Is that correct?

[PJ_in_FL]

Still struggling!

Set DATA range to

Code: Select all

=OFFSET(Database,COUNT(Data!$A:$A)+1,0,1,1)

... the refresh runs but nothing is added to the sheet.

Set DATA range to

Code: Select all

=OFFSET(Database,COUNT(Data!$A:$A)+1,0,1,3)

... I get a 1004 error.

I've rerecorded the import process and "watched" Sheets("Data").QueryTables(1), but I don't see how to specify the destination range without using the QueryTables.Add method, which is what I was trying to eliminate in the first place.

I'm going to explore Hans' suggestion to add the data "auto-manually" using .OpenText. Perhaps using QueryTables for this automation is like using an elephant gun to kill a gnat!

[PJ_in_FL]

Posting a follow-up with a solution in case someone else comes upon this discussion in the future.

Per Hans' suggestion, I worked out the process using Workbooks.OpenText, which turns out to be almost trivial compared to my efforts to force QueryTable to append to an existing dataset.

My working subroutine is:

Code: Select all

Sub GetNewLinks()
Dim vFile As Variant
Dim lRow As Long
Dim rngNew As Range
Dim wbDB As Workbook
Dim wbTXT As Workbook


    lRow = Sheets("Data").Range("Database").End(xlDown).Row + 1
    vFile = GetPropString("_LastTextFile")
    Set rngNew = Sheets("Data").Range("$A$" & lRow)
    Set wbDB = ActiveWorkbook       ' pointer to the database workbook
    
    Workbooks.OpenText _
        Filename:=vFile, _
        DataType:=xlDelimited, _
        Tab:=True
        
    Set wbTXT = ActiveWorkbook      ' newly opened .txt file is now the active workbook
 
    Range("A1").CurrentRegion.Copy Destination:=rngNew
    
    wbTXT.Close SaveChanges:=False
    
    wbDB.Activate                   ' just to be sure we are back to the starting point

End Sub

Hans, thank you again for your insightful direction. I wish I had taken that path earlier!

You can now say "I told you so!"

[HansV]

Glad it works as desired now. Thanks for sharing the solution.

[Jan Karel Pieterse]

Hi PJ,

Just for your reference (since you have it working):

A querytable never appends new imports to existing data, it refreshes itself. There are three options you can select from how it handles the existing data:
1. Insert cells for new data, delete unused cells
2. Insert entire rows for new data, clear unused cells
3. Overwrite extisting cells with new data, clear unused cells.

So if you want to append new imports, choose option 3, have a separate sheet with the existing data, refresh the querytable and copy the new data below the existing data on the other sheet.

[PJ_in_FL]

Jan Karel,

Thanks for the additional information regarding QueryTables. Guess there's always more than one way to dehide the felines!