|
- Sub test0()
- Dim Conn As Object, SQL As String, strConn As String
- Dim Y As Long, Q As Long, M As Long
- Set Conn = CreateObject("ADODB.Connection")
- If Application.Version < 12 Then
- strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="
- Else
- strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source="
- End If
- Conn.Open strConn & ThisWorkbook.FullName
- Worksheets(2).Activate
- ActiveSheet.UsedRange.Offset(2).Clear 'Contents
- Y = Range("J1").Value
- Q = DatePart("Q", DateSerial(Y, Range("J2").Value, 1))
- M = 1 + (Q - 1) * 3
- With Worksheets(1)
- .Range("A1").CurrentRegion.Columns(1).NumberFormatLocal = "@" '2023是数值 2024却是文本 这里统一为文本
- SQL = "SELECT * FROM [" & .Name & "$A2:H] WHERE 年+0=" & Y & " AND 月+0 BETWEEN " & M & " AND " & M + 2
- End With
- Range("A3").CopyFromRecordset Conn.Execute(SQL)
- Range("H2", Cells(Rows.Count, "A").End(xlUp)).Borders.LineStyle = xlContinuous
- Conn.Close
- Set Conn = Nothing
- End Sub
复制代码 |
|