A、根据本工作簿的1个表查询求和写法范本
            Sub 查询方法一()
            Set CONN = CreateObject("ADODB.Connection")
            CONN.Open "provider=microsoft.jet.oledb.4.0;extended
            properties=excel 8.0;data source=" & ThisWorkbook.FullName
            sql = "select 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from
            [sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" &
            Month(Range("F3")) & "' group by 区域,存货类"
            Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)
            CONN.Close: Set CONN = Nothing
            End Sub
            -----------------
            Sub 查询方法二()
            Set CONN = CreateObject("ADODB.Connection")
java源码文件怎么查看            CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
            sql = "select 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from
            [sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" &
            Month(Range("F3")) & "' group by 区域,存货类"
            Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)
            CONN.Close: Set CONN = Nothing
            End Sub
            **************************************************************************************************
  B、根据本工作簿2个表的不同类别查询求和写法范本
            Sub 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()
            Set conn = CreateObject("tion")
            conn.Open "provider=microsoft.jet.oledb.4.0;" & _
                          "extended properties=excel 8.0;data source=" &
            ThisWorkbook.FullName
            Sheet3.Activate
sql update错了怎么恢复
            Sql = " select a.存货类,a.fh ,b.hk from (select 存货类,sum(本月发货数量) " _
properties文件用什么打开                  & " as fh from [入库$] where 存货类 is not null and 区域='" & [b2] _
教学视频背景音乐                  & "' and month(日期)=" & [d2] & " group by 存货类) as a" _
                  & " left join (select 存货类,sum(数量) as hk from [回款$] where 存货类"
            _
                  & " is not null and 区域='" & [b2] & "' and month(开票日期)=" &
            [d2] & "" _
                  & " group by 存货类) as b on a.存货类=b.存货类"
            Range("a5").CopyFromRecordset conn.Execute(Sql)
            End Sub
            *******************************************************************
  C、根据本文件夹下其他工作簿1个表区域的区域求和
8岁编程是学些什么东西            Sub 在工作表1汇总本文件夹下001工作薄的表1分数列查询汇总()
            Set conn = CreateObject("ADODB.Connection")
            conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"
            sql = "select sum(分数) from [sheet1$]"
            Sheets(1).[a2].CopyFromRecordset conn.Execute(sql)
            conn.Close: Set conn = Nothing
            End Sub
            ---------------------
Sub 在工作表1汇总本文件夹下001工作薄的表1A1:A10查询汇总()
seekgirl5            Set conn = CreateObject("ADODB.Connection")
            conn.Open "provider=microsoft.jet.oledb.4.0;extended
            properties='excel 8.0;hdr=no;';data source=" & ThisWorkbook.Path &
            "\001.xls"
            sql = "select sum(f1) from [sheet1$a1:a10]"
            Sheets(1).[A5].CopyFromRecordset conn.Execute(sql)
            conn.Close: Set conn = Nothing
            End Sub
            -----------------------
  Sub 在工作表1汇总本文件夹下001工作薄的表1分数列A1:A7查询并msgbox表达汇总()
            Set conn = CreateObject("ADODB.Connection")
            Set rr = CreateObject("dset")
            conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"
            sql = "select sum(分数) from [sheet1$a1:a7]"
            Sheets(1).[A8].CopyFromRecordset conn.Execute(sql)
            rr.Open sql, conn, 3, 1, 1
            MsgBox rr.fields(0)
            conn.Close: Set conn = Nothing
            End Sub
          ******************************************************************************************
  D、根据本文件夹下其他工作簿多个表区域的单列区域查询求和
            sub 本文件夹下其他工作簿的每个工作簿的第4列 30行查询求和
            Dim cn As Object, f$, arr&(1 To 30), i%
            Application.ScreenUpdating = False
            Set cn = CreateObject("tion")
            f = Dir(ThisWorkbook.Path & "\*.xls")
            Do While f <> ""
                If f <> ThisWorkbook.Name Then
                    cn.Open "provider=microsoft.jet.oledb.4.0;extended
            properties='excel 8.0;hdr=no;';data source=" & ThisWorkbook.Path &
            "\" & f

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。