You need to set a reference to the Ms Excel 12.0 library


Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
Private Sub Command1_Click()
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Sheet1")' Chage this to "pivot1"
For n = 1 To 500
If objWorksheet.Range("A" & n).Value = "678" Then
    MsgBox "Line " & n & " is the value you are looking for"
    Exit For
End If
Next n
End Sub