(1) Open the text file in VBA code, read the first 9 rows and ignore them,
then open a new text file and write each row (beginning with the 10th row)
from the first file into the second file. Close both files, and then import
the second file.
Here's a VBA function to trim the first lines from a text file:
Function TrimFileHeader( _
ByVal FileSpec As String, _
ByVal LinesToTrim As Long, _
Optional ByVal BackupExtension As String = "") As Long
'Removes the specified number of lines from the beginning
'of a textfile.
'Optionally leaves the original file with its extension
'changed to BackupExtension.
'Returns 0 on success, otherwise the number of the error.
Dim fso As Object 'Scripting.FileSystemObject
Dim fIn As Object 'Scripting.TextStream
Dim fOut As Object 'Scripting.TextStream
Dim fFile As Object 'Scripting.File
Dim strFolder As String
Dim strNewFile As String
Dim strBakFile As String
Dim j As Long
On Error GoTo Err_TrimFileHeader
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
'Handle relative path in Filespec
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strNewFile = .BuildPath(strFolder, fso.GetTempName)
'Open files
Set fIn = .OpenTextFile(FileSpec, ForReading)
Set fOut = .CreateTextFile(strNewFile, True)
'Dump header
For j = 1 To LinesToTrim
fIn.ReadLine
Next
'Read and write remainder of file
Do While Not fIn.AtEndOfStream
fOut.WriteLine fIn.ReadLine
Loop
fOut.Close
fIn.Close
'Rename or delete old file
If Len(BackupExtension) > 0 Then
strBakFile = .GetBaseName(FileSpec) _
& IIf(Left(BackupExtension, 1) <> ".", ".", "") _
& BackupExtension
If .FileExists(.BuildPath(strFolder, strBakFile)) Then
.DeleteFile .BuildPath(strFolder, strBakFile), True
End If
Set fFile = .GetFile(FileSpec)
fFile.Name = strBakFile
Set fFile = Nothing
Else
.DeleteFile FileSpec, True
End If
'Rename new file
Set fFile = .GetFile(strNewFile)
fFile.Name = .GetFileName(FileSpec)
Set fFile = Nothing
Set fso = Nothing
End With
'normal exit
TrimFileHeader = 0
Exit Function
Err_TrimFileHeader:
TrimFileHeader = Err.Number
End Function
