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
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
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