我正在尝试将几个excel文件合并为一个。为此,我一直在使用和修改我在这里找到的一个旧答案,但我在Excel 2016 For Mac上运行它时遇到了麻烦(它在Excel 2011 For Mac上工作正常,但有一些更改)。
在Excel 2016(Mac)中,以下代码在循环中运行一次,然后打印选定文件夹中第一个文件的名称,但随后停止。
在Excel 2011(Mac)中,它可以正确打印选定文件夹中所有文件的名称。
Sub wat()
Dim FilesFolder As String, strFile As String
'mac excel 2011
'FilesFolder = MacScript("(choose folder with prompt ""dis"") as string")
'mac excel 2016
FilesFolder = MacScript("return posix path of (choose folder with prompt ""dat"") as string")
If FilesFolder = "" Then Exit Sub
strFile = Dir(FilesFolder)
Do While Len(strFile) > 0
Debug.Print "1. " & strFile
strFile = Dir
Loop
MsgBox "ded"
End Sub
所以,我在这方面还很陌生,但在我看来strFile = Dir
工作不正常。
我看了看Ron de Bruin的页面:循环浏览Mac上文件夹中的文件(适用于Mac Excel的目录)但老实说,这有点太复杂了,我无法理解和修改我的需求。
感谢您的帮助,并感谢您的耐心!
Option Explicit
Sub GetFileNames()
'Modified from http://www.rondebruin.nl/mac/mac013.htm
Dim folderPath As String
Dim FileNameFilter As String
Dim ScriptToRun As String
Dim MyFiles As String
Dim Extensions As String
Dim Level As String
Dim MySplit As Variant
Dim FileInMyFiles As Long
Dim Fstr As String
Dim LastSep As String
'mac excel 2016
'Get the directory
On Error Resume Next 'MJN
folderPath = MacScript("choose folder as string") 'MJN
If folderPath = "" Then Exit Sub 'MJN
On Error GoTo 0 'MJN
'Set up default parameters to get one level of Folders
'All files
Level = "1"
Extensions = ".*"
'Set up filter for all file types
FileNameFilter = "'.*/[^~][^/]*\." & Extensions & "$' " 'No Filter
'Set up the folder path to allow to work in script
folderPath = MacScript("tell text 1 thru -2 of " & Chr(34) & folderPath & _
Chr(34) & " to return quoted form of it's POSIX Path")
folderPath = Replace(folderPath, "'''", "'\''")
'Run the script
ScriptToRun = ScriptToRun & "do shell script """ & "find -E " & _
folderPath & " -iregex " & FileNameFilter & "-maxdepth " & _
Level & """ "
'Set the String MyFiles to the result of the script for processing
On Error Resume Next
MyFiles = MacScript(ScriptToRun)
On Error GoTo 0
'Clear the fist four columns of the current 1st sheet on the workbook
Sheets(1).Columns("A:D").Cells.Clear
'Split MyFiles and loop through all the files
MySplit = Split(MyFiles, Chr(13))
For FileInMyFiles = LBound(MySplit) To UBound(MySplit)
On Error Resume Next
Fstr = MySplit(FileInMyFiles)
LastSep = InStrRev(Fstr, Application.PathSeparator, , 1)
Sheets(1).Cells(FileInMyFiles + 1, 1).Value = Left(Fstr, LastSep - 1) 'Column A - Directory
Sheets(1).Cells(FileInMyFiles + 1, 2).Value = Mid(Fstr, LastSep + 1, Len(Fstr) - LastSep) 'Column B - file name
Sheets(1).Cells(FileInMyFiles + 1, 3).Value = FileDateTime(MySplit(FileInMyFiles)) 'Column C - Date
Sheets(1).Cells(FileInMyFiles + 1, 4).Value = FileLen(MySplit(FileInMyFiles)) 'Column D - size
On Error GoTo 0
Next FileInMyFiles
'Fit the contents
Sheets(1).Columns("A:D").AutoFit
End Sub