VBA - CSV data import: "Run-time error '62': Input past end of file"

[kingofcamden]

Hi all,

I would like to use a macro script to import a (TAB separated) CSV file which was exported from a database. It seems to work - I can see that rows are being imported (although the first cell contains two additional weird characters: ÿþCampaign Name). However the script stops at "Line input #FileNum, ResultStr" with the following error message "Run-time error '62': Input past end of file". This is the code:

Sub Import_Button()

      Dim ResultStr As String
      Dim filename As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      filename = Application.GetOpenFilename("*,*.csv", , "Please select CSV file for January Data to import")
      'Check for no entry or if user clicks cancel
      If filename = "False" Then Exit Sub
      '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
      '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
          Dim splitValues As Variant
          splitValues = Split(ResultStr, vbTab)
          'below format: Sheets("Tab the content should be pasted to").Cells(Counter + ROW, COLUMN)
Sheets("January_Data").Cells(Counter + 49, 1) = Replace(splitValues(0), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 2) = Replace(splitValues(1), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 3) = Replace(splitValues(2), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 4) = Replace(splitValues(3), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 5) = Replace(splitValues(4), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 6) = Replace(splitValues(5), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 7) = Replace(splitValues(6), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 8) = Replace(splitValues(7), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 9) = Replace(splitValues(8), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 10) = Replace(splitValues(9), Chr(34), "")
Sheets("January_Data").Cells(Counter + 49, 11) = Replace(splitValues(10), Chr(34), "")
          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
      MsgBox ("January_Data data successfully imported")
End Sub

My questions are:

• Is the TAB separation the problem? I wasn't really sure what to enter into "splitValues = Split(ResultStr, vbTab)" for the TAB.
• How to get rid of the weird characters in the first cell of the imported file: "ÿþCampaign Name"
• How to stop the error message from showing up: "Run-time error '62': Input past end of file"

Hope you can help!
Many thanks.

 

[eddvrs]

I don't think this is a tab seperation issue. using Split(x, vbTab) should be OK.

I'm not sure about the weird characters, I've had the same issue myself

if you change this line:

Do While Seek(FileNum) <= LOF(FileNum)

to

Do until EOF(FileNum)

that will help with the error message as it will stop at the End Of File

 

[kingofcamden]

Hi eddvrs,

Superb! Thanks - this did the trick!

I read somewhere that the character issue appears because I'm not importing a (unicode) text file...but I can live with the two weird characters, so no problem.

Thanks again and happy Friday!

 

[eddvrs]

No problem mate.

You could try Substitute(x,"ÿþ","") to get rid of them.

OT: Is the Kingof Camden from Camden? I'm down the road in Angel

 

[kingofcamden]

Cool, yes. I'll try! Thanks.

Based near Limehouse, but in Camden quite often Small world!