7,762
社区成员
发帖
与我相关
我的任务
分享
Public Sub XlsMerge(strPath, strPwd, strTargetFile, Optional ByVal IsClear As Boolean = False)
Dim objE As Object
Dim Twb As Object 'New Excel.Workbook
Dim Wb As Object ' New Excel.Workbook
Dim objSheets As Object 'New Worksheet
Dim rng As Object ' Range
Dim s As String
Dim i As Integer
Dim intCount As Integer
On Error Resume Next
'错误判断
If Dir(strPath, vbDirectory) = "" Then
MsgBox "文件路径未找到!", vbOKOnly + vbInformation
Exit Sub
End If
Set objE = CreateObject("Excel.Application")
If Err Then
Err.Clear
MsgBox "创建EXCEL对象出错!", vbOKOnly + vbInformation
Exit Sub
End If
Set Twb = objE.Workbooks.Add
Set objSheets = Twb.Sheets(1)
'调试时显示EXCEL对象
' objE.Visible = True
Wlog "正在生成文件头..."
objSheets.Cells(1, 1) = "姓名"
objSheets.Cells(1, 2) = "操作统计"
objSheets.Cells(1, 3) = "文字统计"
objSheets.Cells(1, 4) = "拼版数量"
objSheets.Cells(1, 5) = "改版数量"
objSheets.Cells(1, 6) = "整版广告"
objSheets.Cells(1, 7) = "头版统计"
Wlog "正在查找文件..."
With objE.FileSearch '查找
.LookIn = strPath '目录
.FileName = "*.xls" '查找所有的xls文件
.Execute '执行查找过程,并且将查询结果按文件名排序
intCount = .FoundFiles.Count
For i = 1 To intCount '在每一个查找到的结果里
s = .FoundFiles(i)
Wlog "正在合并文件:" & Right(s, Len(s) - InStrRev(s, "\") + 1) & "..."
SetProcess i / intCount
Set Wb = objE.Workbooks.Open(s, , False, , strPwd) '打开它
If Err Then
Err.Clear
MsgBox "打开EXCEL文件时出错!", vbOKOnly + vbInformation
Exit Sub
End If
Set rng = objSheets.Range("A" & i + 1, "G" & i + 1)
'复制新打开的工作簿的第一个工作表的已用区域到rng
Wb.Sheets(1).Range("A1:G1").Copy rng
'清空原数据
If IsClear Then
Wb.Sheets(1).Range("B1:G1").Clear
End If
'关闭工作簿
Wb.Close True
If Err Then
Err.Clear
MsgBox "合并EXCEL文件时出错!", vbOKOnly + vbInformation
Exit Sub
End If
Next
End With
SetProcess 1
Wlog "正在保存输出文件..."
Twb.SaveAs strTargetFile
Twb.Close
objE.Quit
Set objE = Nothing
Wlog intCount & "个文件完成合并。"
End Sub
'建立EXCEL对象
'Dim xlApp As New Excel.Application
Dim xlApp As Object
'Dim xlBook As New Excel.Workbook
Dim xlBook As Object
'Dim xlSheet As New Excel.Worksheet
Dim xlSheet As Object
Dim objRs As New ADODB.Recordset
Dim strPWD As String
Dim strTmp As String
Dim strName As String
Dim i As Integer
Dim j As Integer
On Local Error Resume Next
Wlog "正在连接EXCEL..."
Set xlApp = CreateObject("Excel.Application")
Set xlBook = CreateObject("Excel.Workbook")
Set xlSheet = CreateObject("Excel.Worksheet")
'生成工作簿
'开始生成索引
Wlog "正在创建文档..."
Set xlBook = xlApp.Workbooks.Add