地球帝国人口上限:怎样用EXCEL动态调用ACCESS数据 爱问知识人
来源:百度文库 编辑:偶看新闻 时间:2024/04/29 23:46:38
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 = "microsoft.jet.oledb.4.0"
.Open myData
End With
SQL = "SELECT 入库.编号, 入库.物料凭证, 入库.车号, 入库.物料号, 入库.品种, 入库.牌号, " _
& "入库.规格, 入库.定尺, 入库.入库根数, 入库.牌重, 入库.吊号, 入库.炉批号, 入库.库房, " _
& "入库.销售状态, 入库.入库时间, 入库.盘盈, 入库.单价, [牌重]*[单价] AS 金额 " _
& "FROM " & myTable & " WHERE 入库时间<=#" & Sheet2.Range("E11") & "# and 入库时间>=#" & 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单元格的完整示例,希望对你有所启发
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 = "microsoft.jet.oledb.4.0"
.Open myData
End With
SQL = "SELECT 入库.编号, 入库.物料凭证, 入库.车号, 入库.物料号, 入库.品种, 入库.牌号, " _
& "入库.规格, 入库.定尺, 入库.入库根数, 入库.牌重, 入库.吊号, 入库.炉批号, 入库.库房, " _
& "入库.销售状态, 入库.入库时间, 入库.盘盈, 入库.单价, [牌重]*[单价] AS 金额 " _
& "FROM " & myTable & " WHERE 入库时间<=#" & Sheet2.Range("E11") & "# and 入库时间>=#" & 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单元格的完整示例,希望对你有所启发
access调用SQL数据
VB调用Excel数据
Excel怎样才可以调用ACCESS特定字段中含有某一内容的所有数据?
[求助]关于Access调用excel的问题
Flash如何调用Access中的数据?
求用VB调用Access数据
fastreport动态调用数据集的大问题!!!
ASP 动态滚动显示ACCESS数据记录
excel中调用其它表的数据
怎样用EXCEL或ACCESS编一个花名册
怎样用C++将数据写入Access
Excel数据如何导入Access数据库
excel的数据如何导入access
excel数据导入access时发生错误!!!
excel或者access数据查重
excel中的数据导入到access
Excel表导Access数据库数据改变
VB如何调用ACCESS数据库?如何生成EXCEL表?
怎样用Excel求一组数据的平均数?
[[excel]]通过调用公式得到的数据,能[排序???
在WORD中怎样调用EXCEL中的数据
如何把Excel里的数据导入到Access里面
怎样利用VB将EXCEL中的数据导入ACCESS
怎么把Excel中的数据转到Access中(急)