|
发表于 2013-1-6 16:19
|
显示全部楼层
本楼为最佳答案
试试看。
Sub test()
Dim ar()
f_p1$ = "e:\tmp\" '源文件位置
f_p2$ = "e:\tmp\t\" '目标文件位置
f_n$ = Dir(f_p1 & "*.xls*")
Application.ScreenUpdating = 0
ReDim ar(1 To 5)
Do While f_n <> ""
i = i% + 1
If i > UBound(ar) Then ReDim Preserve ar(1 To i + 5)
ar(i) = f_n
f_n$ = Dir
Loop
For i2% = 1 To i
Set wk = Workbooks.Open(f_p1 & ar(i2))
For Each sht In wk.Sheets
If Dir(f_p2 & sht.Name & ".xlsx") = "" Then
sht.Copy
i3% = 4
If Right(ar(i2), 1) = "x" Then i3 = 5
ActiveSheet.Name = Left(ar(i2), Len(ar(i2)) - i3)
ActiveWorkbook.SaveAs Filename:=f_p2 & sht.Name & ".xlsx"
ActiveWorkbook.Close
Else
Set wk2 = Workbooks.Open(f_p2 & sht.Name & ".xlsx")
Set sht2 = wk2.Worksheets.Add(, wk2.Sheets(wk2.Sheets.Count))
i3% = 4
If Right(ar(i2), 1) = "x" Then i3 = 5
sht2.Name = Left(ar(i2), Len(ar(i2)) - i3)
sht.Cells.Copy sht2.[a1]
wk2.Save
wk2.Close
Set wk2 = Nothing
Set sht2 = Nothing
End If
Next
wk.Close
Next
End Sub
就是一些打开关闭打开关闭 |
|