nickshep85
New Member
- Joined
- Mar 21, 2012
- Messages
- 37
I have used the below code from the Microsoft Help site, which allows me to import a 75000 line text file into Excel 2003. What I need is to first add in a header row into the first row of each sheet that is used, but after I added in a header, the import still started from Row 1.
I did try to add in Range("A1:I1").Value = "XXX" when the file is created, hoping that this would work, but I'm being beaten on this one.
Can someone please help me import my data into Row 2 on each sheet that is used?
I did try to add in Range("A1:I1").Value = "XXX" when the file is created, hoping that this would work, but I'm being beaten on this one.
Can someone please help me import my data into Row 2 on each sheet that is used?
Code:
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = ThisWorkbook.Path & "\" & InputBox("Please enter the Text File's name, e.g. test.txt") & ".txt"
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim mypath As String
mypath = ThisWorkbook.Path
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
ActiveWorkbook.SaveAs (mypath & "/PriceFile.xls")
Application.DisplayAlerts = True
[COLOR=#ff0000] '''''''''''''Range("A1:I1").Value = "XXX"[/COLOR]
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add After:=ActiveSheet
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub