SQL
- Function SelectValByNum()
- Dim strShName As String, shData As Worksheet, shResult As Worksheet
- Dim lngRows As Long, arrTitle As Variant
- Dim Conn As Object, Rst As Object, strPath As String
- Dim strConn As String, strSQL As String
- strShName = "数据源"
- Set shData = Sheets(strShName)
- Set shResult = Sheets("结果表")
-
- lngRows = shData.Range("A" & Rows.Count).End(xlUp).Row
- If lngRows < 3 Then lngRows = 3
- strShName = "[" & strShName & "$A3:X" & lngRows & "] "
- arrTitle = Array("成品料号", "料号", "描述", "单价", "币种", "备注", "L/T", "MOQ", "BOM ", "序号", "QTY", "配额", "试产需求数量", "备料方式", "备注")
-
- Set Conn = CreateObject("ADODB.Connection")
- Set Rst = CreateObject("ADODB.Recordset")
- strPath = ThisWorkbook.FullName
- Select Case Application.Version * 1
- Case Is <= 11
- strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & strPath
- Case Is >= 12
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""Excel 12.0;HDR=NO"";"""
- End Select
- Conn.Open strConn
- strSQL = "SELECT '成品1' AS F0, [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F8], [F9], [F10], [F11], [F12], [F23], [F24] " & _
- "FROM " & strShName & _
- "WHERE [F8]<>'' " & _
- "Union All " & _
- "SELECT '成品2' AS F0, [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F13], [F14], [F15], [F16], [F17], [F23], [F24] " & _
- "FROM " & strShName & _
- "WHERE [F13]<>'' " & _
- "Union All " & _
- "SELECT '成品3' AS F0, [F1], [F2], [F3], [F4], [F5], [F6], [F7], [F18], [F19], [F20], [F21], [F22], [F23], [F24] " & _
- "FROM " & strShName & _
- "WHERE [F18]<>'';"
- Rst.Open strSQL, Conn, 3, 1
-
- shResult.UsedRange.ClearContents
- shResult.Range("A1").Resize(1, 15) = arrTitle
- shResult.Range("A2").CopyFromRecordset Rst
- Set Rst = Nothing
- Set Conn = Nothing
-
- MsgBox "OK"
- End Function
复制代码 |