public arrfiles, n as integer
sub 批量提取压缩包内所有文件大小()
dim oapp as shell32.shell
set oapp = new shell32.shell
dim strfile as string
dim i as integer
set rng = application.inputbox(“请选择单元格区域”, type:=8)
if rng.count = 0 then exit sub
set rng = intersect(rng.parent.usedrange, rng)
for each cell in rng
if cell.offset(0, 3) = “zip” then
strfile = cell.hyperlinks(1).address
redim arrfiles(1 to 100)
searchfilesinzip strfile
end if
next
'range(“f2”).resize(1, ubound(arrfiles)) = arrfiles
range(“e1”).resize(ubound(arrfiles), 1) = application.worksheetfunction.transpose(arrfiles)
end sub
sub searchfilesinzip(strfile as string)
dim oapp as shell32.shell
dim filenameinzip, fd
dim ofolder as variant
set oapp = new shell32.shell
set ofolder = oapp.namespace(strfile).items
for each filenameinzip in ofolder
if filenameinzip.isfolder <> true then '判断是否为文件夹,是文件夹则肯能包含子文件
'debug.print filenameinzip.size & filenameinzip
'debug.print filenameinzip
n = n + 1
redim preserve arrfiles(1 to n + 100)
arrfiles(n) = filenameinzip.size & “;” & filenameinzip
else
strfile = filenameinzip.path '路径
searchfilesinzip strfile '子文件
end if
next
end sub
需要引用
Microsoft shell controls and automation