怎样用EXCEL动态调用ACCE
Public Sub 入库()
Dim myData As String, myTable As String, SQL As String
Dim cnn As ADODB。 Connection
Dim rs As ADODB。Recordset
Dim i As Integer
Dim MyBh As String
Application。 ScreenUpdating = False
Application。EnableEvents = False
Sheets("入库查询")。Select
ActiveSheet。 Cells。Clear
myData = ThisWork...全部
Public Sub 入库()
Dim myData As String, myTable As String, SQL As String
Dim cnn As ADODB。
Connection
Dim rs As ADODB。Recordset
Dim i As Integer
Dim MyBh As String
Application。
ScreenUpdating = False
Application。EnableEvents = False
Sheets("入库查询")。Select
ActiveSheet。
Cells。Clear
myData = ThisWorkbook。Path & "\" & Sheets("主控")。Range("E8") & "。mdb"
myTable = "入库"
Set cnn = New ADODB。
Connection
With cnn
。Provider = " t。oledb。4。0"
。Open myData
End With
SQL = "SELECT 入库。
编号, 入库。物料凭证, 入库。车号, 入库。物料号, 入库。品种, 入库。牌号, " _
& "入库。规格, 入库。定尺, 入库。入库根数, 入库。牌重, 入库。吊号, 入库。炉批号, 入库。
库房, " _
& "入库。销售状态, 入库。入库时间, 入库。盘盈, 入库。单价, [牌重]*[单价] AS 金额 " _
& "FROM " & myTable & " WHERE 入库时间=#" & Sheet2。
Range("E10") & "#"
Set rs = New ADODB。Recordset
rs。Open SQL, cnn, adOpenKeyset, adLockOptimistic
For i = 1 To rs。
Fields。Count
Cells(1, i) = rs。Fields(i - 1)。Name
Next i
With Range(Cells(1, 1), Cells(1, rs。
Fields。Count))
。Font。Bold = True
。HorizontalAlignment = xlCenter
End With
Range("A2")。
CopyFromRecordset rs
ActiveSheet。Cells。Font。Size = 10
ActiveSheet。Columns。AutoFit
rs。
Close
cnn。Close
Set rs = Nothing
Set cnn = Nothing
Application。EnableEvents = True
Application。
ScreenUpdating = True
End Sub
上面的这个过程里,包括了使用ADODB打开Access库,用SQL提取记录集,然后写入Excel单元格的完整示例,希望对你有所启发。
收起