Get last line in a CSV file for import

  • Thread starter Thread starter Striker
  • Start date Start date
S

Striker

I have been trying to import a CSV file into Excel 2007 got frustrated and
turned on the macro recorded, below is what it came up with. Not sure if it
can be cleaned up much, but there are 60K to 62K records in this file on a
dialy basis, so I want to add a progress bar. I hope I remember how, but
before I start I need to get the maximum value by finding the last line in
the CSV file. so I can set the maximum value for the progress bar.

How can I get the last line in a CSV file?


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmdGetCSV_Click()

Application.Cursor = xlWait

Application.ScreenUpdating = False

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;C:\test.CSV", Destination:= _

Range("$A$2"))

.Name = "test"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.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 = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = Array(2, 1, 1, 2, 2, 2, 2, 1, 2, 2, 2, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _

1, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _

, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

Application.ScreenUpdating = True

Application.Cursor = xlDefault

End Sub
 
so this is finding, or setting a last row on the excel sheet, not the CSV
that is about to be imported. Not sure where in this recorded macro code I
could add a progress bar? Code below ~~~~~~
 
Back
Top