|
本帖最后由 liulang0808 于 2022-5-14 07:32 编辑
详细请见附件:文件夹里有很多这种EXCEL文件,格式是一样的,但是我要提取文件名,超链接和那个发票号码对应的Sub total:的合计金额。
自己写了一段代码,但不知道哪里错了,详细请见附件,麻烦大神帮忙修改一下。万分感谢
Sub 按钮1_Click()
Dim arr(1 To 5000, 1 To 2)
r = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Offset(1).ClearContents
For Each f In fso.getfolder(ThisWorkbook.Path).Files
If InStr(f.Name, ThisWorkbook.Name) = 0 Then
With Workbooks.Open("D:\发票汇总表\wenti\f.xlsm")
Set Rng = .Sheets(1).UsedRange.Find("Sub total:", Lootat:=xlWhole)
If Not Rng Is Nothing Then
r = r + 1
arr(r, 1) = Rng.Offset(0, 1)
arr(r, 1) = file:///C:\Users\cherry\AppData\Roaming\Tencent\QQTempSys\%W@GJ$ACOF(TYDYECOKVDYB.pngf.Name
End If
.Close False
End With
End If
Next f
If r >= 0 Then
[a2].Resize(r, 2) = arr
End If
Application.ScreenUpdating = True
End Sub
|
|