|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Option Explicit
- Public Sub 合并()
- Dim arrFilesname, el, C As Range
- Dim wb As Workbook
- Const P = "E:\新版数据-财务\1成本核算文件\2成本核算"
- '需要合并的xls文件的路径。
- On Error Resume Next
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- arrFilesname = ListFile(P, False, "*.xls")
- ThisWorkbook.Worksheets("成本目录").Range("e2:f" & Rows.Count).ClearContents
- For Each el In arrFilesname
- Err.Clear
- Set wb = GetObject(el)
- If Err.Number = 0 Then
- Set C = wb.Worksheets("成本").Cells.Find(what:="总成本")
- If Not C Is Nothing Then
- With ThisWorkbook.Worksheets("成本目录").Cells(Rows.Count, 5).End(xlUp)
- .Offset(1).Value = wb.Name
- .Offset(1, 1).Value = C.EntireRow.Cells(1, 3).Value
- End With
- End If
- End If
- wb.Close
- Next
- Set wb = Nothing
- End Sub
- 'MuLu是要查找的文件夹,如:"F:\VBA\pdf\Excel2007VBA"
- 'LeiXing是要查找的文件类型,如:*.xls,a?*.txt等,如果省略该参数,函数实现的是查找文件夹功能
- 'LeiXing参数不省略时:1、Zi为true时搜索所有子文件夹下符合要求的文件。2、Zi为false时仅搜索参数MuLu下符合要求的文件
- 'LeiXing参数省略时: 1、Zi为true时搜索参数MuLu下所有子文件。2、Zi为false时仅搜索参数MuLu下的文件夹
- '函数的返回值是一个一维数组,可视具体情况使用
- Public Function ListFile(MuLu As String, Zi As Boolean, Optional LeiXing As String = "")
- Dim MyFile As String, ms As String
- Dim arr, brr, x
- Dim i As Integer, D As Object
- Set D = CreateObject("Scripting.Dictionary")
- If Left(MuLu, 1) <> "" Then MuLu = MuLu & ""
- D.Add MuLu, ""
- i = 0
- Do While i < D.Count
- brr = D.keys
- MyFile = Dir(brr(i), vbDirectory)
- Do While MyFile <> ""
- If MyFile <> "." And MyFile <> ".." Then
- If (GetAttr(brr(i) & MyFile) And vbDirectory) = vbDirectory Then D.Add (brr(i) & MyFile & ""), ""
- End If
- MyFile = Dir
- Loop
- If Zi = False Then Exit Do
- i = i + 1
- Loop
- If LeiXing = "" Then
- ListFile = Application.Transpose(D.keys)
- Else
- For Each x In D.keys
- MyFile = Dir(x & LeiXing)
- Do While MyFile <> ""
- ms = ms & x & MyFile & ","
- MyFile = Dir
- Loop
- If Zi = False Then Exit For
- Next
- If ms = "" Then ms = "没有符合要求的文件,"
- ListFile = Application.Transpose(Split(ms, ","))
- End If
- End Function
复制代码
我有下面一段代码,提取指定文件夹下面的XLS文件,并且建立超链接,但是由于文件中存在外联数据的情况,所以运行中老是弹出如下的对话框,请问怎么可以屏蔽掉(启用更新),请高手帮修改下 |
|