Concerting a Macro to run on a Mac

[arShort]

Hello,

I am trying to get some code to work on a mac, and am getting a Run-time error '5' invalid procedure or argument on line 18 "Value=Dir(Folderpath, &H1F)"

I realize that this means that some part of that code is not available in Excel 2011 for Mac, but am unsure which part and was wondering if anyone knew a work around.

Public CWB As WorkbookPublic PWB As Workbook
Public PRWB As String
Public PMWB As String

Sub ListFiles()
Dim cell As Range, selcell As Range
Dim Value As String
Dim Folder As Variant, a As Long
ReDim Folders(0)
Set cell = Range("A4")
Set selcell = Selection
Range("A4:B10000").Value = ""
Folderpath = Range("B1").Value
If Right(Folderpath, 1) <> "\" Then
  Folderpath = Folderpath & "\"
End If
[U][B]Value = Dir(Folderpath, &H1F)[/B][/U]
Do Until Value = ""
    If Value <> "." And Value <> ".." Then
        If GetAttr(Folderpath & Value) <> 16 Then
            If Right(Value, 4) = ".xls" Or Right(Value, 5) = ".xlsx" Or Right(Value, 5) = ".xlsm" Then
                cell.Offset(0, 0).Value = Value
                cell.Offset(0, 1).Value = FileLen(Folderpath & Value)
                Set cell = cell.Offset(1, 0)
            End If
        End If
    End If
Value = Dir
Loop
Call Addcheckboxes
selcell.Select
End Sub

Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
Application.ScreenUpdating = False
ActiveSheet.CheckBoxes.Delete
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For cell = 4 To LRow
    If Cells(cell, "A").Value <> "" Then
        CLeft = Cells(cell, "C").Left
        CTop = Cells(cell, "C").Top
        CHeight = Cells(cell, "C").Height
        CWidth = Cells(cell, "C").Width
        ActiveSheet.OptionButtons.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell
Application.ScreenUpdating = True
End Sub

Sub OpenFiles()
Dim Folderpath As String
Dim cell As Range
Dim r, LRow As Single
'Public CWB As Workbook
'Public PWB As Workbook
Application.ScreenUpdating = False


PMWB = ActiveWorkbook.Name


Folderpath = Range("B1").Value
Set CWB = ActiveWorkbook
If Right(Folderpath, 1) <> "\" Then
  Folderpath = Folderpath & "\"
End If
For Each chkbx In ActiveSheet.OptionButtons
    If chkbx.Value = 1 Then
        For r = 1 To Rows.Count
            If Cells(r, 1).Top = chkbx.Top Then
                Workbooks.Open Filename:=Folderpath & Range("A" & r).Value
                PRWB = ActiveWorkbook.Name
                'Code Running
                    Application.Run "Setup"
                    Application.Run "Search"
                Windows(PMWB).Activate
                Sheets("Basic Info").Select
                Exit For
            End If
        Next r
        'CWB.Activate
    End If
Next
End Sub

 

[arShort]

Any help would be greatly appreciated

 

[Jim Gordon]

A quick glance indicates you are using Windows file path syntax on a Mac. That will never work in Mac OS X. Excel MVP Ron DeBruin has a site that has lots of examples on how to work with files on a Mac. Here's the URL: Excel 2011 for the Mac Tips