VBA Excel函数用于以字节为单位返回文件大小

I wish to return the file size of some files in the same folder or in a different one with VBA in Excel 2010.

我希望在Excel 2010中使用VBA返回同一文件夹或不同文件中某些文件的文件大小。

3 个解决方案

#1

There is a very nice and simple VBA function, which was not mentioned so far, FileLen:

有一个非常好用且简单的VBA函数，到目前为止还没有提到，FileLen：

FileLen("C:\Temp\test file.xls")

FileLen（“C：\ Temp \ test file.xls”）

It returns the size of the file in bytes.

它以字节为单位返回文件的大小。

In combination with looping through files in a directory it's possible to achieve what you originally wanted (get sizes of files in a folder).

结合循环目录中的文件，可以实现您最初想要的（获取文件夹中文件的大小）。

#2

Here how to use it in Excel Cell:

这里如何在Excel Cell中使用它：

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

If you have a german Windows than:

如果您有德国Windows而不是：

=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)

以下是VBA模块的功能:(只需启用开发人员工具，然后将其复制并粘贴到新模块中）

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 ***

#3

This is in reply to the question regarding the use of the semicolon ; as a parameter seperator.

这是对使用分号的问题的答复;作为参数分隔符。

In locals (like US) which use the point/fullstop . as a decimal point, the coma , is used as a list seperator, in this case as a seperator in the list of parameters to VBA functions and procedures.

在使用point / fullstop的本地人（如美国）。作为小数点，昏迷用作列表分隔符，在这种情况下作为VBA函数和过程的参数列表中的分隔符。

In other locals (like DE and Europe in general), where the decimal point is the coma , things would get confusing if the same symbol was used as a list seperator, so the semcolon ; is being used instead

在其他本地人（如一般的DE和欧洲）中，小数点是昏迷，如果使用相同的符号作为列表分隔符，那么事情会变得混乱，所以semcolon;正在被使用

#1