|
由于文本文档的信息比较多,请教下大侠们如何用VBA实现。
将文本里的exe格式的链接地址导入到excel指定位置,其它格式的链接不要,并且把exe格式
的链接地址中的最后一个斜杠和下划线里的文件名导入到excel指定位置中并在文件名末尾添加“.exe”
附件如下:
用字典试了下去重,果然少了很多,我把相同的exe文件去掉了。 - Sub tt()
- Set d = CreateObject("scripting.dictionary")
- Dim f As Integer
- Dim arr(1 To 10000, 1 To 2)
- f = FreeFile
- mypath = ThisWorkbook.Path
- Open mypath & "\地址.txt" For Input As #f
- Do While Not EOF(f) '把文本内容全部读入数组arr
- Line Input #f, a
- aa = LCase(a)
- If InStr(aa, "http") > 0 And InStr(aa, ".exe") > 0 Then
- a1 = InStr(aa, "http"): a2 = InStr(aa, ".exe")
- If a2 > a1 Then a = Mid(a, a1, a2 + 3 - a1 + 1) '下载地址:http:\\。。。。。.exe
- br = Split(a, "/"): b = br(UBound(br))
- b = Split(b, "_")(0) & ".exe" '文件名称:。。。。。.exe
- If Not d.exists(b) Then
- n = n + 1
- arr(n, 1) = b
- arr(n, 2) = a
- d(b) = ""
- End If
- End If
- Loop
- Close #f
-
- With ActiveSheet
- .Rows("2:10000").ClearContents
- .[a2].Resize(n, 2) = arr
- End With
- End Sub
复制代码
|
|