Now has the same folder a format of the same table 1, table 2 and table 3, now I need a new table 5 in the three tables of data obtained automatically, when the folder a to add a table 4 of the same format, the data in table 5 and will automatically update, should be how to operate

CodePudding user response:

You the automatic servo, refresh the folder regularly, when changes occurred in the file in folder folder, just drop the data updating in table 5

CodePudding user response:

Sub HzwWb ()
Dim bt As Range, r, As Long As Long c
R=1 'header lines
C=4 'header columns
Range (Cells (r + 1, "a"), Cells (1024576 c)). ClearContents' clear summary of the central plains data
Application. ScreenUpdating=False
Dim filename As String, wb As Workbook, erow As Long, fn As String, arr As the Variant
Filename=Dir (ThisWorkbook Path & amp; "\ *. XLS")
The Do While filename & lt;> "
"If filename & lt;> ThisWorkbook. Name Then 'to determine whether the file work book
Erow=Range (" a1 ") CurrentRegion. Rows. Count + 1 'made the first empty line Numbers in the summary table
Fn=ThisWorkbook. Path & amp; "" & amp; Filename
Set wb=GetObject (fn) 'will be fn represent the workbook variable assigned to wb
Set SHT=wb. Worksheets (1) 'summary is the first sheet of each workbook
'the data in the table records in arr variable
Arr=SHT. Range (SHT) Cells (r + 1, "A"), SHT) Cells (1024576), "B"). The End (xlUp). Offset (0, 1) c)
'will be arr data writing summary table
Cells (erow, "A"). The Resize (UBound (arr, 1), UBound (arr, 2))=arr
Wb. Close False
End the If
Filename=Dir 'Dir function in other file name, and is assigned to the variable
Loop
Application. ScreenUpdating=True
End Sub



Who moved here for your reference * * * * * * * * * * * * * * * * * * * * * * * * *