本文介绍了用于以字节返回文件大小的VBA Excel函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望在Excel 2010中将某些文件的文件大小恢复在同一个文件夹或与VBA不同的文件中。我如何解决这个问题?

解决方案

这里如何在Excel中使用它单元格:

  = GetDirOrFileSize(C: \\Users\xxx\Playground\,filename.xxx)

如果你有一个德国Windows比:

  = GetDirOrFileSize(C:\Users\xxx\Playground\;filename .xxx)

这是VBA模块的功能:(只需启动开发人员工具,函数GetDirOrFileSize(strFolder As String,可选strFile As Variant)As Long $ b $($)


$ b'调用序列:GetDirOrFileSize(drive \path[,filename.ext])

Dim lngFSize As Long,lngDSize As Long
Dim oFO As Object
Dim oFD As Object
Dim OFS As Object

lngFSize = 0
Set OFS = CreateObject(Scripting.FileSystemObject)

如果strFolder =然后strFolder = ActiveWorkbook.path
如果Right(strFolder,1) > \然后strFolder = strFolder& \
'感谢Jean-Francois Corbett,你也可以使用OFS.BuildPath(strFolder,strFile)

如果OFS.FolderExists(strFolder)然后
如果不是IsMissing(strFile)然后

如果OFS.FileExists(strFolder& strFile)然后
设置oFO = OFS.Getfile(strFolder& strFile)
GetDirOrFileSize = oFO.Size
如果

Else
设置oFD = OFS.GetFolder(strFolder)
GetDirOrFileSize = oFD.Size
如果

结束如果

结束函数'*** GetDirOrFileSize ***


I wish to return the file size of some files in the same folder or in a different one with VBA in Excel 2010. How can i solve this issue?

解决方案

Here how to use it in Excel Cell:

 =GetDirOrFileSize("C:\Users\xxx\Playground\","filename.xxx")

If you have a german Windows than:

=GetDirOrFileSize("C:\Users\xxx\Playground\";"filename.xxx")

Here is the function for the VBA modul: (Just enable the Developer tools, and copy and paste this into a new modul)

Function GetDirOrFileSize(strFolder As String, Optional strFile As Variant) As Long

'Call Sequence: GetDirOrFileSize("drive\path"[,"filename.ext"])

   Dim lngFSize As Long, lngDSize As Long
   Dim oFO As Object
   Dim oFD As Object
   Dim OFS As Object

   lngFSize = 0
   Set OFS = CreateObject("Scripting.FileSystemObject")

   If strFolder = "" Then strFolder = ActiveWorkbook.path
   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
   'Thanks to Jean-Francois Corbett, you can use also OFS.BuildPath(strFolder, strFile)

   If OFS.FolderExists(strFolder) Then
     If Not IsMissing(strFile) Then

       If OFS.FileExists(strFolder & strFile) Then
         Set oFO = OFS.Getfile(strFolder & strFile)
         GetDirOrFileSize = oFO.Size
       End If

       Else
        Set oFD = OFS.GetFolder(strFolder)
        GetDirOrFileSize = oFD.Size
       End If

   End If

End Function   '*** GetDirOrFileSize ***

这篇关于用于以字节返回文件大小的VBA Excel函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-18 22:42