8 DIR函数操作文件夹

Sub wjhb()

Dim str As String

Dim wb As Workbook

Dim i, j As Integer   'i用来计算数据源的表有多少行  j用来计算汇总表目前有多少行数据，例如有10行，就从11行开始复制数据

str = Dir("d:\data\*.xls*")

For i = 1 To 100

   Set wb = Workbooks.Open("d:\data\" & str)

'上面是壳子###################################################################

    i= wb.Sheets(1).Range("a65535").End(xlUp).Row

    j= ThisWorkbook.Sheets("数据").Range("a65535").End(xlUp).Row

   wb.Sheets(1).Range("a2:g" & i).Copy ThisWorkbook.Sheets("数据").Range("a" & j + 1)  '数据源的数据复制到汇总表里面

   ThisWorkbook.Sheets("数据").Range("h"& j + 1).Resize(i - 1, 1) = Split(wb.Name, ".")(0)  '从汇总表复制数据那一行开始，用resize往下选i-1行，里面全写上城市名

'下面是壳子###################################################################

   wb.Close

   str = Dir

   If str = "" Then

       Exit For

   End If

Next

End Sub

Sub zz()

Dim i, m

As Integer

Dim str

As String

Dim sht

As Worksheet

Range("a1:a1000").EntireRow.ClearContents

Application.DisplayAlerts

= False

Application.ScreenUpdating

= False

str =

Dir("e:\数据\数据2\*.xls*")

For i =

1 To 100

    Set wb = Workbooks.Open("e:\数据\数据2\" & str)

    wb.Sheets(1).Copyafter:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name= Split(wb.Name, ".")(0)

    wb.Close

    str = Dir()

    If str = "" Then

    Exit For

    End If

Next

For m =

2 To Sheets.Count

    Sheets(m).Select

    Range("A2").Select

    Range(Selection,Selection.End(xlToRight)).Select

    Range(Selection,Selection.End(xlDown)).Select

    Selection.Copy

    Sheets(1).Select

    Range("A" &Range("a65400").End(xlUp).Row + 1).Select

    ActiveSheet.Paste

Range("h"&Range("h65400").End(xlUp).Row+1).Resize(Sheets(m).Range("a65400").End(xlUp).Row- 1, 1) = Sheets(m).Name

    Next

For Each

sht In Sheets

    If sht.Name <> "数据" Then

    sht.Delete

    End If

Next

Application.DisplayAlerts

= ture

Application.ScreenUpdating

= ture

End Sub