用什么函数可以链接到EXCEL

SUQUANJIN

请教一下:  有时ACCESS和EXCEL及WORD都是配合着用的,,有一些数据和表格是用EXCEL做的,在ACCESS中要怎么样才能用命令打开EXCEL的工作表呢?

andymark

下面是一个ACCESS操作EXCEL的例子

1. Sub FileImportFromExcel(ByVal sFile As String)
   
2. 
   
3. Dim appExcel As Excel.Application
   
4. Dim wbk As Excel.Workbook
   
5. Dim wks As Excel.Worksheet
   
6. Dim Rs As New ADODB.Recordset
   
7. Dim Conn As New ADODB.Connection
   
8. Dim I As Integer
   
9. Dim J As Integer
   
10. Dim StrWeekName As String
    
11. 
    
12. Set appExcel = Excel.Application
    
13. Set wbk = appExcel.Workbooks.Open(sFile)
    
14. 
    
15. Set wks = appExcel.Worksheets("NB LIPC")
    
16. 
    
17. Set Conn = CurrentProject.Connection
    
18. 
    
19. On Error Resume Next
    
20. 
    
21. Rs.Open "Select * from TemCommitFromExcel", Conn, adOpenDynamic, adLockOptimistic
    
22. 
    
23. For I = 1 To 500
    
24. 
    
25. If wks.Cells(I, 1).Value = "Technology" Then
    
26. Rs.AddNew
    
27. Rs.Fields("Year") = Me.CmbYear
    
28. Rs.Fields("Site") = Me.TxtSite
    
29. Rs.Fields("RequirementWeek") = Me.CmbRequirementWeek
    
30. Rs.Fields("PartNumber") = Trim(wks.Cells(I, 5).Value)
    
31. 
    
32. For J = 0 To 13
    
33. StrWeekName = "WK" & J
    
34. Rs.Fields(StrWeekName) = wks.Cells(I + 5, 2 + J).Value
    
35. Next J
    
36. 
    
37. Rs.Update
    
38. End If
    
39. 
    
40. Next I
    
41. 
    
42. Set wks = Nothing
    
43. wbk.Close True
    
44. Set wbk = Nothing
    
45. appExcel.Quit
    
46. Set appExcel = Nothing
    
47. Set Rs = Nothing
    
48. Set Conn = Nothing
    
49. 
    
50. Exit Sub
    
51. 
    
52. 
    
53. End Sub

复制代码