|
楼主 |
发表于 2023-10-14 09:01
|
显示全部楼层
每天必须学习SQL基本知识,争取十天在进步。
从零开始学习SQL(图文教程)-Excel VBA程序开发-ExcelHome技术论坛 - https://club.excelhome.net/forum ... =1123322&page=3
Where and方法调试通过。
Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
Where Between and 方法没有通过。是不是日期形式只能用Where ## abd ##
Where 日期 Between #" & Format(oDate, "yyyy/mm") & "# and #" & Format(oDate + 32, "yyyy/mm") & "#"
Select 目标文件夹, Format(日期,'yyyy年mm月dd日 h:mm:ss'),Format(日期,'yyyy年mm月') From [Sheet1$A1:Z47627] Where 日期 Between #2022/09# and #2022/10#
- Sub SheetToSheet()
- Dim Rs As Recordset, Rs1 As Recordset
- Dim Str, ShtStr
- Dim Sht As Worksheet
- Dim oDate As Date
- Set Sht = Sheet1
- ShtStr = "From [" & Sht.Name & "$]"
- Str = "Select Distinct Format(表名,'yyyy年mm月') " & ShtStr & "Where 表名 Is Not Null "
- Set Rs = SqlRetuRs(Str)
- Sheet2.Cells(6, 1).CopyFromRecordset Rs
- With Rs
- .MoveFirst
- For ii = 0 To .RecordCount - 1
- oDate = .Fields(0)
- Str = "Select Format(日期,'yyyy年mm月'), Format(日期,'yyyy年mm月dd日 h:mm:ss') ,目标文件夹 " & _
- " From [Sheet1$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
- 'Str = "Select Format(日期,'yyyy年mm月'), 目标文件夹 " & _
- " From 6[Sheet1$] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
- Str = "Select 目标文件夹, Format(日期,'yyyy年mm月dd日 h:mm:ss'),Format(日期,'yyyy年mm月') " & _
- " From [Sheet1$A1:Z47627] Where 日期 >= #" & Format(oDate, "yyyy/mm") & "# and 日期 < #" & Format(oDate + 32, "yyyy/mm") & "#"
- Str = "Select 目标文件夹, Format(日期,'yyyy年mm月dd日 h:mm:ss'),Format(日期,'yyyy年mm月') " & _
- " From [Sheet1$A1:Z47627] Where 日期 Between #" & Format(oDate, "yyyy/mm") & "# and #" & Format(oDate + 32, "yyyy/mm") & "#"
- Debug.Print Str
- Sheet1.Activate
- Set Rs1 = SqlRetuRs(Str)
- With Rs1
- Str = .Fields(1)
- Set Sht = ThisWorkbook.Sheets(Str)
- With Sht
- .Activate
- .Cells.Clear
- .Cells.Font.Size = 7
- End With
- End With
- Sht.Cells(4, 1).CopyFromRecordset Rs1
- .MoveNext
- Next ii
- End With
- End Sub
复制代码
|
|