Excel模板与Access数据怎样链接？？

sxfwang

通过对象引用,把Excel文件打开,你想改什么都可以啊!

waltonw

能否说的详细一点,是不是在Access中操作呢？好像没有对象引用啊？

HG

waltonw：您就摸著石頭過一次河吧，不過怎知水深淺。

waltonw

好!!我这就去再试一次。谢了!

waltonw

谢谢!基本上已可以实现.

zhengjialon

我也十分要学习这方面的知识，请各位高手多指点，小弟谢了。

waltonw

模板和链接

大頭

Private Sub cmdExcel_Click()
  On Error GoTo ErrorHandler
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strOrderDate As Date
   Dim strAddress As String
   Dim strCustomerName  As String
   Dim strWorksheetPath As String
   Dim strTemplatePath As String
   Dim bks As Excel.Workbooks
   Dim wks As Excel.Worksheet
   Dim strWorksheet As String
   Dim appExcel As Object
   Dim strsql As String
   Dim intOrderID As Long
   Dim strProductName As String
   Dim dblUnitPrice As Double
   Dim dblQuantity As Double
   Dim dblDiscount As Double
   Dim intRow As Integer
   Set appExcel = GetObject(, "Excel.Application")
   strTemplatePath = CurrentProject.Path & "\"
   strWorksheet = "OrderDetails.xlt"
   strWorksheetPath = strTemplatePath & strWorksheet
   Set bks = appExcel.Workbooks
   bks.Add strWorksheetPath
   'Create variables for information to write to worksheet
   strCustomerName = Me![CustomerID]
   strAddress = Me![ShipCity] & Me![ShipAddress]
   strOrderDate = Me![OrderDate]
   Set wks = appExcel.ActiveSheet
   'Write to cells in worksheet
   With wks
      .Range("C2").Value = strCustomerName
      .Range("C3").Value = strAddress
      .Range("C4").Value = strOrderDate
   End With

   'Use the ADO connection And Access data
   Set cn = CurrentProject.AccessConnection
   Set rs = New ADODB.Recordset
   intOrderID = Me![OrderID]
  strsql = " SELECT [Order Details].OrderID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount "
  strsql = strsql & " FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID "
  strsql = strsql & " WHERE [Order Details].OrderID = " & intOrderID
   With rs
      Set .ActiveConnection = cn
      .Source = strsql
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
  intRow = 0
Do Until rs.EOF
    strProductName = rs![ProductName]
    dblUnitPrice = rs![UnitPrice]
    dblQuantity = rs![Quantity]
    dblDiscount = rs![Discount]
   'Use Offset To Locate Cell
    wks.Range("B7").Offset(intRow, 0).Value = strProductName
    wks.Range("B7").Offset(intRow, 2).Value = dblQuantity
    wks.Range("B7").Offset(intRow, 3).Value = dblDiscount
    wks.Range("B7").Offset(intRow, 4).Value = dblUnitPrice
    wks.Range("B7").Offset(intRow, 5).Value = dblUnitPrice * dblQuantity * (1 - dblDiscount)
    intRow = intRow + 1
    rs.MoveNext
Loop

   'Make worksheet visible
   appExcel.Application.Visible = True

   Set rs = Nothing
ErrorHandlerExit:
   Exit Sub

ErrorHandler:

   If Err = 429 Then
      'Excel is not running; open Excel with CreateObject
      Set appExcel = CreateObject("Excel.Application.10")
      Resume Next
   Else
      MsgBox "Error No:        " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

[此贴子已经被作者于2003-12-25 0:48:35编辑过]

大頭

差點忘了
[em07]

[此贴子已经被作者于2003-12-25 0:40:00编辑过]

huanghai

使用EXCEL类，拼命模板文件生成新文件，然后使用ADO读相关记录，并填到对应的单园格，最后生成一个新的EXCEL文件。