Dir() 函数在 Mac Excel 2011 VBA 中不起作用 [英] Dir() function not working in Mac Excel 2011 VBA

问题描述

您好，我正在尝试列出 Excel 工作簿所在子目录中的所有文件.出于某种原因，代码无法在 Dir 函数之外执行.任何人都可以请指教吗?谢谢！

Sub ListFiles()ActiveSheet.Name = "临时"Dim MyDir 作为字符串'声明变量Dim strPath 作为字符串Dim strFile 作为字符串昏暗如长MyDir = ActiveWorkbook.Path '工作簿所在的当前路径strPath = MyDir &":Current:" 'Current" 文件夹子目录中的文件，我使用的是 Mac Excel 2011'在 A、B 和 C 列中插入标题单元格(1，A").值=文件名"单元格(1，B").值=大小"Cells(1, "C").Value = "日期/时间"'查找下一个可用的行r = Cells(Rows.Count, "A").End(xlUp).Row + 1'从文件夹中获取第一个文件'注意:宏在这里停止工作strFile = Dir(strPath & "*.csv", vbNormal)'遍历文件夹中的每个文件做而 Len(strFile) >0'列出当前文件的名称、大小和日期/时间Cells(r, 1).Value = strFileCells(r, 2).Value = FileLen(strPath & strFile)Cells(r, 3).Value = FileDateTime(strPath & strFile)'确定下一行r = r + 1'从文件夹中获取下一个文件strFile = 目录环形'改变列的宽度以达到最佳拟合Columns.AutoFit结束子

解决方案

Gianna，你不能像在 VBA-EXCEL 2011 中那样使用 DIR.我的意思是不支持通配符.为此，您必须使用 MACID.

查看此代码示例(尝试和测试)

子样本()MyDir = ActiveWorkbook.PathstrPath = MyDir &":"strFile = Dir(strPath, MacID("TEXT"))'遍历文件夹中的每个文件做而 Len(strFile) >0If Right(strFile, 3) = "csv" 然后Debug.Print strFile万一strFile = 目录环形结束子

有关 MACID 的更多详细信息，请参阅此链接

主题:MacID 函数

链接:http://office.microsoft.com/en-us/access-help/macid-function-HA001228879.aspx

万一我怀疑链接失效了，这里是一个摘录.

<块引用><块引用><块引用><块引用>

MacID 函数

在 Macintosh 上用于将 4 个字符的常量转换为 Dir、Kill、Shell 和 AppActivate 可能使用的值.

语法

MacID(常量)

必需的常量参数由 4 个字符组成，用于指定资源类型、文件类型、应用程序签名或 Apple 事件，例如，文本、OBIN、Excel 文件的XLS5"(Excel 97 的XLS8")，Microsoft Word 使用W6BN"(Word 97 为W8BN")，依此类推.

备注

MacID 与 Dir 和 Kill 一起使用以指定 Macintosh 文件类型.由于 Macintosh 不支持 * 和 ?作为通配符，您可以使用四字符常量来标识文件组.例如，以下语句从当前文件夹中返回 TEXT 类型的文件:

Dir("SomePath", MacID("TEXT"))

MacID 与 Shell 和 AppActivate 一起使用，以使用应用程序的唯一签名指定应用程序.

HTH

Hi I am trying to list all the files in a subdirectory of where the Excel workbook is residing in. For some reason, the code cannot execute beyond the Dir function. Can anyone please advise? Thank you!

Sub ListFiles()

    ActiveSheet.Name = "temp"

    Dim MyDir As String
    'Declare the variables
    Dim strPath As String
    Dim strFile As String
    Dim r As Long

    MyDir = ActiveWorkbook.Path 'current path where workbook is
    strPath = MyDir & ":Current:" 'files within "Current" folder subdir, I am using Mac Excel 2011

    'Insert the headers in Columns A, B, and C
    Cells(1, "A").Value = "FileName"
    Cells(1, "B").Value = "Size"
    Cells(1, "C").Value = "Date/Time"

    'Find the next available row
    r = Cells(Rows.Count, "A").End(xlUp).Row + 1

    'Get the first file from the folder
            'Note: macro stops working here
    strFile = Dir(strPath & "*.csv", vbNormal)

    'Loop through each file in the folder
    Do While Len(strFile) > 0

        'List the name, size, and date/time of the current file
        Cells(r, 1).Value = strFile
        Cells(r, 2).Value = FileLen(strPath & strFile)
        Cells(r, 3).Value = FileDateTime(strPath & strFile)

        'Determine the next row
        r = r + 1

        'Get the next file from the folder
        strFile = Dir

    Loop

    'Change the width of the columns to achieve the best fit
    Columns.AutoFit

End Sub

解决方案

Gianna, you cannot use DIR like that in VBA-EXCEL 2011. I mean the wildcards are not supported. You have to use MACID for this purpose.

See this code sample (TRIED AND TESTED)

Sub Sample()
    MyDir = ActiveWorkbook.Path
    strPath = MyDir & ":"

    strFile = Dir(strPath, MacID("TEXT"))

    'Loop through each file in the folder
    Do While Len(strFile) > 0
        If Right(strFile, 3) = "csv" Then
            Debug.Print strFile
        End If

        strFile = Dir    
    Loop
End Sub

See this link for more details on MACID

Topic: MacID Function

Link: http://office.microsoft.com/en-us/access-help/macid-function-HA001228879.aspx

EDIT:

In case that link ever dies which I doubt, here is an extract.

MacID Function

Used on the Macintosh to convert a 4-character constant to a value that may be used by Dir, Kill, Shell, and AppActivate.

Syntax

MacID(constant)

The required constant argument consists of 4 characters used to specify a resource type, file type, application signature, or Apple Event, for example, TEXT, OBIN, "XLS5" for Excel files ("XLS8" for Excel 97), Microsoft Word uses "W6BN" ("W8BN" for Word 97), and so on.

Remarks

MacID is used with Dir and Kill to specify a Macintosh file type. Since the Macintosh does not support * and ? as wildcards, you can use a four-character constant instead to identify groups of files. For example, the following statement returns TEXT type files from the current folder:

Dir("SomePath", MacID("TEXT"))

MacID is used with Shell and AppActivate to specify an application using the application's unique signature.

HTH

这篇关于Dir() 函数在 Mac Excel 2011 VBA 中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！