批量合并Excel文件与数据录入宏

Public Sub data_entry()

' Macro recorded 12/26/2007 by cn0k0710

Dim i As Integer '获取的数据的最后行号
Dim i1 As Integer '汇总的文件的空白行号
Dim i2 As Integer '打开的文件个数

Dim Fname As String '文件的名称
Dim Sname As String '表格页的名称
Dim userfilename As String
Dim WB As Workbook

Dim count As Integer '用户名列表和行号，循环查询时用
Dim Count1 As Integer '用户名工作表名称，循环查询时用

Dim Sumsheets As Integer '工作表的数量

Dim name2 As String 'test

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'通过名城列表获取文件名，容易和sc2文件夹中的文件名不一致
'For count = 6 To 7 '4 to 17 人员列表
'    Fname = Range("C" & count).Value
'        If Fname = "" Then
'            wb.Close
'            GoTo ErrorHandler
'        End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'通过手动录入获取文件名

'Fname = Application.InputBox("Enter a Fname")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 Sheet1.Select
 Range("B2:I1000").Select
 Selection.Value = ""
 

 FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Please select the files...", , True)
    If IsArray(FileToOpen) = 0 Then
        'MsgBox "没有选择文件"
        MsgBox "No files are selected!"
        Exit Sub
    End If

 For i2 = 1 To UBound(FileToOpen)
       'Open a worksheet
'GoOn:
    userfilename = FileToOpen(i2)
    Set WB = Workbooks.Open(userfilename)
    userfilename = WB.name
    Fname = GetUserName(userfilename)
        If Fname = "" Then
            WB.Close
            GoTo ErrorHandler
        End If
  
    
  'Workbooks.Open filename:="D:\Daily Work\performance appraisal\SC2\" & Fname & ".xls" '打开文件
  
    Windows(userfilename).Activate