PDA

View Full Version : Mac EXCEL 2011 VBA



andysmith
12-12-2011, 02:50 PM
has anyone experience of the read all files in a folder to an array in EXCEL 2011 VBA on Mac?
Also i need to know the same problem for all Folders and SubFolders on Mac. I know that all for a long time for Microsoft Windows Systems, but now i need a kind of parallel universe.

THX for help!!

Here are the test in moment:

Option Explicit
Sub Searching()
Dim strAsterix As String, strFileNames() As String, strFileName As String, lngArrIndex As Long, varHilfs As Variant, blnResult As Boolean
Dim objThisHere As Workbook, lngFileCount As Long, intPosPlace As Integer, strRootDir As String, lngArrayCount As Long
Dim myFSO As Object, strScriptToRun As String
Set objThisHere = ThisWorkbook

If Not Application.OperatingSystem Like "*Mac*" Then
'Windows Path
strRootDir = "m:" & Application.PathSeparator & "Test" & Application.PathSeparator
Set myFSO = CreateObject("Scripting.FileSystemObject")
If Not fncFolderExists(strRootDir) Then
MsgBox ("Folder dosen't exists, please proof")
Exit Sub
End If
ElseIf Application.OperatingSystem Like "*Mac*" Then
If Val(Application.Version) >= 14 Then
strRootDir = "music:Test"
strScriptToRun = strScriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
strScriptToRun = strScriptToRun & "exists strFolder " & Chr(34) & strRootDir & Chr(34) & Chr(13)
strScriptToRun = strScriptToRun & "end tell" & Chr(13)
blnResult = MacScript(strScriptToRun)
If blnResult = False Then
MsgBox ("Folder dosen't exists")
Exit Sub
End If
End If
Else
MsgBox ("unknow system" & Chr(13) & Application.OperatingSystem)
End If

strAsterix = "*.*"
lngArrIndex = 0
ReDim strFileNames(lngArrIndex)

strFileName = Dir(strRootDir & strAsterix)
Do While strFileName <> ""
If UCase(Right(strFileName, 4)) = UCase(".xls") Then
lngArrIndex = lngArrIndex + 1
ReDim Preserve strFileNames(lngArrIndex)
strFileNames(lngArrIndex) = strFileName
End If
strFileName = Dir
Loop
End Sub
Public Function fncFolderExists(strFolder As String) As Boolean
If Right(strFolder, 1) <> Application.PathSeparator Then strFolder = strFolder & Application.PathSeparator
On Error Resume Next
fncFolderExists = False
fncFolderExists = Dir(strFolder, vbDirectory) <> "" And strFolder <> ""
On Error GoTo 0
End Function