Dir()函数在Mac Excel 2011 VBA中不起作用 [英] Dir() function not working in Mac Excel 2011 VBA
问题描述
Sub ListFiles()
ActiveSheet.Name =temp
Dim MyDir As String
'声明变量
Dim strPath As String
Dim strFile As String
Dim r As Long
MyDir = ActiveWorkbook.Path'当前路径,其中工作簿是
strPath = MyDir& :当前:'文件在当前文件夹subdir中,我使用的是Mac Excel 2011
'在列A,B和C中插入标题
单元格(1,A ).Value =FileName
Cells(1,B)。Value =Size
Cells(1,C)。Value =Date / Time
'查找下一个可用行
r = Cells(Rows.Count,A)。End(xlUp).Row + 1
'从文件夹中获取第一个文件
'注意:宏停止工作
strFile = Dir(strPath&.csv,vbNormal)
'循环通过文件夹
中的每个文件Do While Len(strFile)> 0
'列出当前文件的名称,大小和日期/时间
单元格(r,1).Value = strFile
单元格(r,2).Value = FileLen(strPath& strFile)
单元格(r,3).Value = FileDateTime(strPath& strFile)
'确定下一行
r = r + 1
'从文件夹中获取下一个文件
strFile = Dir
循环
'更改列的宽度以实现最佳fit
Columns.AutoFit
End Sub
Gianna,您不能在VBA-EXCEL 2011中使用 DIR
。我的意思是不支持通配符。您必须使用MACID才能达到此目的。
请参阅此代码示例( TRIED AND TESTED )
Sub Sample()
MyDir = ActiveWorkbook.Path
strPath = MyDir&
strFile = Dir(strPath,MacID(TEXT))
'循环通过文件夹中的每个文件
Do While Len(strFile) > 0
如果右(strFile,3)=csv然后
Debug.Print strFile
如果
strFile = Dir
循环
End Sub
有关MACID的详细信息,请参阅此链接
主题:MacID功能
链接: http://office.microsoft.com/en-us/access-help/macid-function-HA001228879.aspx
编辑:
如果链接死了,我怀疑,这是一个摘录。
MacID功能
在Macintosh上用于将4个字符的常量转换为Dir,Kill,Shell和AppActivate可能使用的值。
语法
MacID(常量)
所需的常量参数包含4个字符,用于指定资源类型,文件类型,应用程序签名或Apple事件例如,TEXT,OBIN,Excel文件的XLS5(Excel 97的XLS8),Microsoft Word使用W6BN(W8BNfor Word 97)等等。
备注
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屋!