You could try the following
Code:
Private Sub cmdImportReport_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim StrLoc As String
On Error GoTo ErrorTrap:
StrLoc = "C:\Documents and Settings\Desktop\test report.xls"
cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & StrLoc & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
strSQL = "Select * from [Sheet1$]"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
Range("a1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
ErrorTrap:
Select Case Err
Case -2147467259
MsgBox "the file is not present at this location"
End Select
End Sub
All you need to ensure is that you include a reference to "Microsoft ActiveX Data Objects 2.8 Library"
If you need the headers displayed, then this can be done as well.. let me know if this works for you.. PLease not that in the StrSql statement, between the square brackets should be the name of the sheet you are wanting to import