Question

VBA实现 复制文件夹下所有excel文件的第一个sheet内容到一个汇总excel中

假设一个文件夹下有100个excel，格式是csv，需要将所有excel文件的第20到40行按顺序汇总到一个总的excel的一个sheet表中，感谢大神帮忙

Answer 1

试试看：

Public Sub data_entry()
Dim FileToOpen As Variant
Dim i As Integer '打开的文件个数
Dim userfilename As String
Dim Wb As Workbook 'workbook for data loop
Dim Wb1 As Workbook 'sum workbook
Dim Ws1 As Worksheet 'sum worksheet

Dim count As Integer 'for data input loop
Set Wb1 = ThisWorkbook
Set Ws1 = ActiveSheet
'停止更新链接,屏幕闪烁及报警事件
Application.AskToUpdateLinks = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

FileToOpen = Application.GetOpenFilename("Excel Files (*.csv), *.csv", , "Please select the files...", , True)

If IsArray(FileToOpen) = 0 Then
'MsgBox "没有选择文件"
MsgBox "No files are selected!"
Ws1.Unprotect
GoTo ErrorHandler
End If

For i = 1 To UBound(FileToOpen)
userfilename = FileToOpen(i)
Set Wb = Workbooks.Open(userfilename)
Ws1.Rows(Ws1.Range("A65536").End(xlUp).Row + 1 & ":" & Ws1.Range("A65536").End(xlUp).Row + 21).Value = Wb.Worksheets(1).Rows("20:40").Value
Wb.Close
Next i
Range("B3").Select
ErrorHandler:
Application.AskToUpdateLinks = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

追问

是要新建个什么格式的excel文件吗

追答

xls就可以，或者xlsm以保证脚本可以运行