|
楼主 |
发表于 2013-7-6 21:37
|
显示全部楼层
hwc2ycy 发表于 2013-7-6 21:02
其实数字偏长的你在前面加个单引号问题就解决了,昨天合并后我就看到了。
- Sub 合并工作表()
- Dim strPath As String, strFile As String
- Dim objwb As Workbook, rg As Range
- Dim arr, strMsg As String
- On Error GoTo ErrorHandler
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .EnableEvents = False
- .Calculation = xlCalculationManual
- End With
- strPath = ThisWorkbook.Path & Application.PathSeparator
- strFile = Dir(strPath & "*.xls")
- Do While Len(strFile)
- Set rg = Sheet1.Cells(Rows.Count, 1).End(xlUp)
- If strFile <> ThisWorkbook.Name Then
- strMsg = strMsg & strFile & vbCr
- With GetObject(strPath & strFile)
- If rg.Row <> 1 Then
- arr = .Worksheets("sheet1").UsedRange.Offset(1).Value
- Else
- arr = .Worksheets("sheet1").UsedRange.Value
- End If
- Windows(.Name).Visible = True
- .Close False
- End With
- If rg.Row = 1 Then
- rg.Resize(UBound(arr), UBound(arr, 2)).Value = arr
- Else
- rg.Offset(1).Resize(UBound(arr), UBound(arr, 2)).Value = arr
- End If
- End If
- strFile = Dir
- Loop
- With rg.CurrentRegion.Borders
- .LineStyle = 1
- .ColorIndex = 16
- End With
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- If Len(strMsg) Then
- MsgBox "合并完成" & vbCrLf & "导入的文件如下:" & vbCrLf & strMsg
- End If
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & Err.Description
- Err.Clear
- If ActiveWorkbook.Name <> ThisWorkbook.Name Then
- ActiveWorkbook.Close False
- End If
- End Sub
|
|