Convert PDF header to Excel headers

I received the following code from this forum:

Sub Test()
Sheets(1).Activate
Sheets(2).Cells.Clear
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(N, 1) <> "" Then
        If Left(Cells(N, 3), 3) = "DOB" Then
            StudentID = Left(Cells(N, 1), InStr(Cells(N, 1), " ") - 1)
            StudentName = Trim(Right(Cells(N, 1), Len(Cells(N, 1)) - Len(StudentID)))
            DOB = Right(Cells(N, 3), Len(Cells(N, 3)) - 5)
        ElseIf IsNumeric(Cells(N, 1)) Then
            TargetRow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
            Sheets(2).Cells(TargetRow, 1) = StudentID
            Sheets(2).Cells(TargetRow, 2) = StudentName
            Sheets(2).Cells(TargetRow, 3) = DOB
            Sheets(2).Cells(TargetRow, 4) = Cells(N, 3)
            Sheets(2).Cells(TargetRow, 5) = Cells(N, 6)
            Sheets(2).Cells(TargetRow, 6) = Cells(N, 8)
            Sheets(2).Cells(TargetRow, 7) = Cells(N, 9)
            Sheets(2).Cells(TargetRow, 8) = Cells(N, 10)
            Sheets(2).Cells(TargetRow, 9) = Cells(N, 11)
            Sheets(2).Cells(TargetRow, 10) = Cells(N, 12)
            Sheets(2).Cells(TargetRow, 11) = Cells(N, 13)
            Sheets(2).Cells(TargetRow, 12) = Cells(N, 1)
        End If
    End If
Next N
End Sub

This code has helped me tremendously. But now I need it for another PDF sheet that is formatted a little different and I cannot seem to get it to work. I have attached a sample of the info with the VBA coding. I have a workbook that has 2500 students formatted this way. I need to be able to put it in a format that I can upload to a database. Thank you.

If the following are always the case then try the code below:

1) Cell A1 always contains the same information (ID #, Student Name and Grade in that order)
2) The exam results always start in row 4 (there can be any number of them but they must start in row 4).

If the above conditions vary then you'll need to specify the possible variations so they can be programmed for.

Sub Test()

Sheets(1).Activate
Sheets(2).Cells.Clear

StudentID = Mid(Cells(1, 1), 4, InStr(Cells(1, 1), "Name:") - 7)
StudentName = Mid(Cells(1, 1), InStr(Cells(1, 1), "Name:") + 6, InStr(Cells(1, 1), "Grade:") - InStr(Cells(1, 1), "Name:") - 7)
StudentGrade = Right(Cells(1, 1), 2)

For N = 4 To Sheets(1).Cells(4, 1).End(xlDown).Row
        If IsNumeric(Cells(N, 3)) Then
            TargetRow = Sheets(2).Cells(5000, 1).End(xlUp).Row + 1
            Sheets(2).Cells(TargetRow, 1).Value = StudentID
            Sheets(2).Cells(TargetRow, 2).Value = StudentName
            Sheets(2).Cells(TargetRow, 3).Value = StudentGrade
            Sheets(2).Cells(TargetRow, 4).Value = Cells(N, 2).Value
            Sheets(2).Cells(TargetRow, 5).Value = Cells(N, 3).Value
            Sheets(2).Cells(TargetRow, 6).Value = Cells(N, 4).Value
        End If
Next N

End Sub

I notice you don't extract the exam type (maths, science etc). Is that not important?

Hope that helps.

Dion

Ok, that did what I needed, almost. It gave me three more studentID, Name and grade that I did not need. Also, yes the exam results are always in row 4. Yes, the exam information needs to be extracted. I ran this on my main page and it only took the first student. I have over 2500 student in this workbook. Each one of the students is formatted the same way the first one is. Thank you for your help.

Well I have been messing with the coding and this is what I have so far:

Sub Test()

Sheets(1).Activate
Sheets(2).Cells.Clear

StudentID = Mid(Cells(1, 1), 4, InStr(Cells(1, 1), "Name:") - 7)
StudentName = Mid(Cells(1, 1), InStr(Cells(1, 1), "Name:") + 6, InStr(Cells(1, 1), "Grade:") - InStr(Cells(1, 1), "Name:") - 7)
StudentGrade = Right(Cells(1, 1), 2)

For N = 4 To Sheets(1).Cells(4, 1).End(xlDown).Row
        If IsNumeric(Cells(N, 3)) Then
            TargetRow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
            Sheets(2).Cells(TargetRow, 1).Value = StudentID
            Sheets(2).Cells(TargetRow, 2).Value = StudentName
            Sheets(2).Cells(TargetRow, 3).Value = StudentGrade
            Sheets(2).Cells(TargetRow, 4).Value = Cells(N, 1).Value
            Sheets(2).Cells(TargetRow, 5).Value = Cells(N, 2).Value
            Sheets(2).Cells(TargetRow, 6).Value = Cells(N, 3).Value
            Sheets(2).Cells(TargetRow, 7).Value = Cells(N, 4).Value
        End If
    Next N
End Sub

But it is giving me the following:

100110	Acosta, Jacob Z 	12	Math	4/26/2010	11	2195
100110	Acosta, Jacob Z 	12	Reading	4/26/2010	11	2402
100110	Acosta, Jacob Z 	12	Science	4/26/2010	11	2304
100110	Acosta, Jacob Z 	12	Social Studies	4/26/2010	11	2404
100110	Acosta, Jacob Z 	12	Writing	4/3/2006	7	2399
100110	Acosta, Jacob Z 	12				
100110	Acosta, Jacob Z 	12	Id: 100516      Name: Alanis JR, Julio Cesar  Grade: 12			
100110	Acosta, Jacob Z 	12	State Rpt Id: 637341673                         Bldg: 2

As you can see it gives me three extra StudentID and Name for the same student. Then it goes to the next set of data and gives it to me exactly how it looks in the PDF. Not sure what I am doing wrong.