|
已经解决这个问题 采用的sql语句查询打开的采购单的字段名称有没有品牌 然后生成sql语句查询。
- Option Explicit
- Sub tj3()
- '声明变量
- Dim conn, 结果记录集, 字段名称记录集
- Dim 文件路径 As String, 文件名 As String, 订单号 As String, 项目名称 As String, 订购日期 As String, strsql As String, FieldsName As String, s As String, StrConn As String
- Dim i As Long, j As Long
- Dim Bol_PinPai As Boolean
- Dim FieldsNames()
- Application.ScreenUpdating = False '关闭屏幕刷新
- '创建后期引用对象变量
- Set conn = CreateObject("ADODB.Connection")
- Set 结果记录集 = CreateObject("ADODB.Recordset")
- Set 字段名称记录集 = CreateObject("ADODB.Recordset")
- StrConn = "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName '连接字符串
- conn.Open StrConn '打开数据库连接
- Range("a3:l65536").ClearContents '清除统计表历史数据
- 文件路径 = ThisWorkbook.Path & "" '获取当前工作表路径
- 文件名 = Dir(文件路径 & "*.xls") '获取当前路径下要汇总的Excel文件名
- Do While 文件名 <> ""
- If 文件名 <> ThisWorkbook.Name Then '非本文件
- strsql = "select * from [" & 文件路径 & 文件名 & "].[order$a10:g19]"
- With 结果记录集
- .Open strsql, conn, 1, 3
- .movelast
- .movefirst
- 订单号 = Mid(.Fields(0).Value, 6)
- .movelast
- 项目名称 = Mid(.Fields(0).Value, 6)
- .movefirst
- End With
- Debug.Print "============================================="
- strsql = "select * from [" & 文件路径 & 文件名 & "].[order$a23:i24]"
- FieldsName = ""
- With 字段名称记录集
- .Open strsql, conn, 1, 3
- .movelast
- .movefirst
- If .RecordCount = 1 Then
- Bol_PinPai = False
- ReDim FieldsNames(1 To .Fields.Count)
- For i = LBound(FieldsNames) To UBound(FieldsNames)
- FieldsNames(i) = "" '初始化字段名称数组为空
- Next i
- For i = 1 To .Fields.Count
- FieldsNames(i) = .Fields(i - 1).Value '填入字段名称到数组
- If Not Bol_PinPai Then
- If .Fields(i - 1).Value = "品牌" Then Bol_PinPai = True '判断有无品牌关键字
- End If
- Next i
- FieldsName = IIf(Bol_PinPai, "", " 品名, '', ") '根据是否有品牌关键字 拼凑不同的查询字段表
- For i = IIf(Bol_PinPai, 2, 3) To UBound(FieldsNames)
- FieldsName = FieldsName & IIf(FieldsNames(i) <> "", FieldsNames(i) & ",", "'',")
- Next i
- FieldsName = Left(FieldsName, Len(FieldsName) - 1) '去掉最后一位逗号
- End If
- .Close '关闭对象
- End With
- s = IIf(Bol_PinPai, "有品牌", "无品牌")
- If IsNull(结果记录集.Fields(6).Value) Then '读取日期
- 订购日期 = Mid(结果记录集.Fields(5).Value, 6)
- Rem strsql = "select 序号,'" & 订单号 & "','" & 订购日期 & "','" & 项目名称 & "'," & FieldsName & " from [" & 文件路径 & 文件名 & "].[order$a24:i65536] where not 交货日期 is null"
- Else
- 订购日期 = Mid(结果记录集.Fields(6).Value, 6)
- Rem strsql = "select 序号,'" & 订单号 & "','" & 订购日期 & "','" & 项目名称 & "'," & FieldsName & " from [" & 文件路径 & 文件名 & "].[order$a24:i65536] where not 交货日期 is null"
- End If
- Rem 将上面得到的字符串拼接为动态查询语句
- strsql = "select 序号,'" & 订单号 & "','" & 订购日期 & "','" & 项目名称 & "'," & FieldsName & " from [" & 文件路径 & 文件名 & "].[order$a24:i65536] where not 交货日期 is null"
- Debug.Print "【" & 文件名 & "----" & s & "】 SQL:"; strsql '打印最终查询的sql语句到立即窗口
- Range("a" & [b65536].End(3).Row + 1).CopyFromRecordset conn.Execute(strsql) '把查询结果复制到工作表
- 结果记录集.Close '关闭对象
- End If
- 文件名 = Dir() '获取下一个文件名
- Loop
- conn.Close '关闭对象
- Set 结果记录集 = Nothing
- Set 字段名称记录集 = Nothing
- Set conn = Nothing
- [a3] = 1
- [a3].AutoFill Range("a3:a" & [b65536].End(3).Row), xlFillSeries
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|