[已解决]是一段合并多个单元格的程序，求解释

lyq123d · 发表于 2013-6-8 16:24

本帖最后由 lyq123d 于 2013-6-17 18:12 编辑

是一段合并多个单元格的程序，现在自己复制过来可以使用了，但是不知道没一句的具体含义，为了提高自己，求各位高手解释一下每一句的含义：

Sub 合并当前目录下所有工作簿的全部工作表()

Dim MyPath, MyName, AWbName

Dim Wb As Workbook, WbN As String

Dim G As Long

Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & "\" & "*.xls")

AWbName = ActiveWorkbook.Name

Num = 0

Do While MyName <> ""

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & "\" & MyName)

Num = Num + 1

With Workbooks(1).ActiveSheet
.Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)

For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)

Wb.Close False

End With

End If

MyName = Dir

Loop

Range("A1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

End Sub

最佳答案

suxiong

2013-6-8 16:36

大概说一下：
Dim MyPath, MyName, AWbName '定义了这几个变量
Dim Wb As Workbook, WbN As String '定义了这几个变量
Dim G As Long '变量
Dim Num As Long'变量
Dim BOX As String'变量
Application.ScreenUpdating = False'关闭屏幕刷新，这样可以加快运行速
MyPath = ActiveWorkbook.Path'MyPath的值为当前激活工作表
MyName = Dir(MyPath & "" & "*.xls")''用了DIR，DIR返回指定路径文件的名称
AWbName = ActiveWorkbook.Name'这里是给AWbName付值
Num = 0
Do While MyName <> ""'循环
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "" & MyName)’打开工作表,里面两个变量前面是有付值的，分别为路径与名字
Num = Num + 1
With Workbooks(1).ActiveSheet .Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)'(.Range("A65536").End(xlUp).Row + 2, 1)最后一个单元格的行+2，第一列， Left为右截取函数，参数据分别为取哪个，取多长；
For G = 1 To Sheets.Count'给G值值为，1到SHEET的个数据
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)’UsedRange已用单元格
Next
WbN = WbN & Chr(13) & Wb.Name'chr(13) 可以生成回车符
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("A1").Select
Application.ScreenUpdating = True''开启屏幕刷新
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

复制代码

suxiong · 发表于 2013-6-8 16:36

大概说一下：
Dim MyPath, MyName, AWbName '定义了这几个变量
Dim Wb As Workbook, WbN As String '定义了这几个变量
Dim G As Long '变量
Dim Num As Long'变量
Dim BOX As String'变量
Application.ScreenUpdating = False'关闭屏幕刷新，这样可以加快运行速
MyPath = ActiveWorkbook.Path'MyPath的值为当前激活工作表
MyName = Dir(MyPath & "" & "*.xls")''用了DIR，DIR返回指定路径文件的名称
AWbName = ActiveWorkbook.Name'这里是给AWbName付值
Num = 0
Do While MyName <> ""'循环
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "" & MyName)’打开工作表,里面两个变量前面是有付值的，分别为路径与名字
Num = Num + 1
With Workbooks(1).ActiveSheet .Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)'(.Range("A65536").End(xlUp).Row + 2, 1)最后一个单元格的行+2，第一列， Left为右截取函数，参数据分别为取哪个，取多长；
For G = 1 To Sheets.Count'给G值值为，1到SHEET的个数据
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)’UsedRange已用单元格
Next
WbN = WbN & Chr(13) & Wb.Name'chr(13) 可以生成回车符
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("A1").Select
Application.ScreenUpdating = True''开启屏幕刷新
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

复制代码

lyq123d · 发表于 2013-6-17 18:11

suxiong 发表于 2013-6-8 16:36

搞明白了，谢谢。

suxiong · 发表于 2013-6-17 18:59

lyq123d 发表于 2013-6-17 18:11
搞明白了，谢谢。

好吧！给最佳

		自动登录	找回密码
密码			注册