Office中国论坛/Access中国论坛

标题: 用什么函数可以链接到EXCEL [打印本页]

---

作者: SUQUANJIN    时间: 2007-12-12 15:57
标题: 用什么函数可以链接到EXCEL
请教一下:  有时ACCESS和EXCEL及WORD都是配合着用的,,有一些数据和表格是用EXCEL做的,在ACCESS中要怎么样才能用命令打开EXCEL的工作表呢?

---

作者: andymark    时间: 2007-12-12 16:05
下面是一个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

复制代码

---

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)

Powered by Discuz! X3.3