檔案夾中的回圈可以作業，但回圈單元格不起作用，不能從50個Excel作業簿中復制和粘貼選定的資料到單個目標Excel作業簿。我在Windows作業系統中作業。我有一個有50個Excel檔案的檔案夾。我有一個單一的目標Excel檔案。資料從檔案夾轉移到一個單一的Excel檔案。
請提供幫助。
Sub Combine()
Dim s As String, MyFiles As String.
Dim endd As Integer, startt As Integer.
Dim NewWb As 作業簿
Dim newS As 作業表
Dim i As Long
Application.DisplayAlerts = False As Long
應用程式.ScreenUpdating = False
Set NewWb = Workbooks.Add
With NewWb
Set newS = NewWb.Worksheets("Лист1"/span>)
結束 與
endd = i * 10 1
startt = endd - 10 1
MyFiles = "C:UsersUserDesktopNezavisimaiPapka2"/span>
s = Dir(MyFiles & "*.xlsx")
Do While s <> ""
[a1] = 0
If Dir = ""/span> Then Exit Sub Else i = 1
做
If Dir = "" Then Exit Do Else i = i 1
Loop Until False
[a1] = i
With Workbooks.Open(MyFiles & s)
.Worksheets("Данные").Range("A1：C10").Copy
.關閉 SaveChanges:=False。
結束 與
newS.Select[/span
With newS.
.Range("B"/span> & startt & ":D"/span> & endd).Paste
結束 與
s = Dir
回圈 回圈
應用程式.DisplayAlerts = True
應用程式.ScreenUpdating = True
End Sub
uj5u.com熱心網友回復：
Option Explicit
Sub Combine()
Const FOLDER = "C:UsersUserDesktopNezavisimaiPapka2"
Dim wb As 作業簿, wbNew As 作業簿, wsNewAs 作業表
Dim檔案名As String, i As Long, n As Integer, rng As范圍
Set wbNew = Workbooks.Add(xlWBATWorksheet) '1 sheet[/span
Set wsNew = wbNew.Sheet(1)
Application.ScreenUpdating = False1
filename = Dir(FOLDER & "*.xlsx")
Do While filename < > ""
' open book and copy range
Set wb = Workbooks.Open(FOLDER & filename, False, True) ' 沒有鏈接更新，只讀。
Set rng = wb.Sheet(1).Range("A1:C10")
rng.Copy wsNew.Range("B"/span> & i)
i = i rng.Rows.Count
' close book goto next[/span
n = n 1
檔案名 = Dir
Loop
' 保存合并
wbNew.SaveAs ThisWorkbook.Path & "Combined.xlsx"
wbNew.Close False[/span
應用程式.ScreenUpdating = True
MsgBox n & " files copied", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/324161.html
標籤：