|
- Option Explicit
- Sub Test()
- Dim WB As Workbook
- Dim DicSht, DicWB
- Dim Path$, I&
- Set DicSht = CreateObject("Scripting.Dictionary")
- Set DicWB = CreateObject("Scripting.Dictionary")
- Set WB = ThisWorkbook
- Path = ThisWorkbook.FullName
- '===========Sht===============
- For I = 1 To Sheets.Count
- DicSht(Sheets(I).Name) = I
- Next I
- '============WB==============
- Dim Ar()
- Ar = Sheet1.UsedRange
- For I = 2 To UBound(Ar)
- If DicWB(Ar(I, 1)) = "" Then
- DicWB(Ar(I, 1)) = I - 1
- End If
- Next I
- '=============================
- Application.SheetsInNewWorkbook = DicSht.Count
- Dim KeyWB, Sql$
- For Each KeyWB In DicWB.keys
- With Workbooks.Add
- Application.StatusBar = "正在生成" & KeyWB & "请等待 "
- For I = 1 To DicSht.Count
- Sheets(I).Name = DicSht.keys()(I - 1)
- Sql = "select * from [" & Path & "].[" & Sheets(I).Name & "$]"
- Sql = Sql & " where 营服中心 = '" & KeyWB & "'"
- Call SQL执行(Path, .Sheets(I), Sql)
- Next I
-
- .SaveAs Filename:=WB.Path & "" & KeyWB
- .Close
- End With
-
- Next
- Application.SheetsInNewWorkbook = 3
- End Sub
- Sub SQL执行(WbNamePath As String, Sht, SqlStr As String)
- Dim conn As Object, rst As Object
- Dim StrConn As String, strSQL As String
- Dim I As Integer, PathStr As String
- Set conn = CreateObject("ADODB.Connection")
- Set rst = CreateObject("ADODB.Recordset")
- On Error GoTo 出错提示
- PathStr = WbNamePath '设置工作簿的完整路径和名称
- Select Case Application.Version * 1 '设置连接字符串,根据版本创建连接
- Case Is <= 11 '03以下版本包含03
- StrConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
- Case Is >= 12 '07以上版本包含07
- StrConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
- End Select
- conn.Open StrConn '打开数据库链接
- strSQL = SqlStr '设置SQL查询语句
- Set rst = conn.Execute(strSQL) '执行查询,并将结果输出到记录集对象
- With Sht '对存放结果的工作表操作
- .Cells.ClearContents '清除全表内容
- For I = 0 To rst.Fields.Count - 1 '填写标题
- .Cells(1, I + 1) = rst.Fields(I).Name
- Next I
- .Range("A2").CopyFromRecordset rst
- End With
- rst.Close: conn.Close '关闭数据库连接
- Set conn = Nothing: Set rst = Nothing '释放对象变量
- Exit Sub
- 出错提示:
- MsgBox Err.Description, , "雪山飞狐温馨提示"
- End Sub
复制代码 |
|