Sub CreateRecordset1()
Application.Calculation = xlManual
Dim cnn As Object
Dim rst As Object
Dim strPath As String
Dim strSQL As String
Dim lngCount As Long
Dim i As Integer
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.RecordSet")
'----后期引用Recordset对象
strPath = ThisWorkbook.FullName
'----指定ADO连接的文件路径(本工作簿)
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Extended Properties=Excel 12.0;" _
& "Data Source=" & strPath
strSQL = "select distinct a.*,b.工艺设计理论单产 from (select distinct 工厂,物料,max(提交日期) as 最新日期 from [MX$] group by 工厂,物料) as a left join [MX$] as b on a.工厂=b.工厂 and a.物料=b.物料 and (a.最新日期=b.提交日期 or a.最新日期 is null)" '定义SQL记录命令"
'----SQL语句
rst.Open strSQL, cnn, 1, 3
'----使用Open方法建立记录集
Cells.ClearContents
For i = 0 To rst.Fields.Count - 1
'----遍历读取记录集中的字段
Cells(1, i + 1) = rst.Fields(i).Name
Next i
Range("A2").CopyFromRecordset rst
'----读取记录集中的记录
lngCount = rst.RecordCount
'----记录的数目
MsgBox "共查询到:" & lngCount & "条记录。"
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.Calculation = xlAutomatic '打开自动计算
End Sub
表头() EXCEL2007 工作表名称:MXSub CreateRecordset1()Application.Calculation = xlManual Dim cnn As Object Dim rst As Object Dim strPath As String Dim strSQL As String Dim lngCount As ...