asked on
Receiving Error in Macro Function

Within my function ISODateToExcel I am receiving a error for some reason saying "Argument not optional" for the line below:

theDate = DateSerial(Left(DTString, 5), Mid(DTString, 7, 2), Mid(DTString, , 2))

I've placed the code below as well.
Sub Select_File_Or_Files_Mac()
    Dim MyPath As String
    Dim MyScript As String
    Dim MyFiles As String
    Dim MySplit As Variant
    Dim N As Long
    Dim Fname As String
    Dim mybook As Workbook
    Dim filePath As Variant
    Dim dataRange As Range 'Range in the import sheet to get data from (excludes header row)
    Dim destRange As Range 'Range in the destination sheet to write to (end of usedrange.rows +1)
    
    'On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
   If MyFiles <> "" Then
        MySplit = Split(MyFiles, ",")
            Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
            destRange.Offset(1).Clear

        For Each filePath In MySplit
            Set mybook = Application.Workbooks.Open(filePath, , True, , , , , , , , , , False) 'open read-only. Don't add to MRU
            testval = mybook.Sheets(1).Cells(1, 2).Value 'Check the value of cell A2 to determine the version of the import sheet
            Debug.Print testval
            Set dataRange = mybook.Sheets(1).UsedRange.Offset(1)
            Set dataRange = dataRange.Resize(dataRange.Rows.Count - 1)
            Debug.Print dataRange.Address
            Set destRange = ThisWorkbook.Sheets("Import").UsedRange
            'Clear content of Current sheet apart from headers
'            If testval = "SR Number" Or testval = "ID" Then
'                destRange.Offset(1).Clear
'                Set destRange = destRange.Resize(1, 1)
'            End If
            If testval = "Subject" Or testval = "Problem Summary" Then
                destRange.Offset(2).Clear
                Set destRange = destRange.Resize(2, 2)
            End If
            Set destRange = destRange.Offset(destRange.Rows.Count).Resize(dataRange.Rows.Count)
            Debug.Print destRange.Address
            If testval = "Ticket Status" Then 'ge-ticket-history-view-2015-03-31' (Zendesk file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(3).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(4).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(6).Value 'Assignee
                destRange.Columns(5).Value = dataRange.Columns(2).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(5).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(30).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(8).Value 'Request Date
                destRange.Columns(10).Value = dataRange.Columns(9).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(10).Value 'Channel
                destRange.Columns(12).Value = Now()
            ElseIf testval = "Problem Summary" Then 'SEARCH-SR-PRODUCT-GROUPS (EMC Support file type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(2).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(3).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(4).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(5).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(7).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(9).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(8).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(10).Value 'Create Date
                destRange.Columns(10).Value = dataRange.Columns(11).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(27).Value 'Creation Channel
                For Each c In destRange.Columns(8).Cells
                    c.Value = ISODateToExcel(c.Value)
                Next
                destRange.Columns(12).Value = Now()
            ElseIf testval = "SR#" Then 'US Bank (Q Tool type)
                destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(10).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(14).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(13).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(30).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(4).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(16).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(17).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
          ElseIf testval = "Create Date" Then 'Broadridge (EMC Service Center type)
                destRange.Columns(1).Value = dataRange.Columns(1).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(9).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(8).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(13).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(14).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(4).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(5).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(2).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(15).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
       ElseIf testval = "Read?" Then 'Morgan Stanley (EMC Service Center type)
                destRange.Columns(1).Value = dataRange.Columns(3).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(4).Value 'Problem Summary
                destRange.Columns(3).Value = dataRange.Columns(5).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(21).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(6).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(8).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(10).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(7).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(11).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(12).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(22).Value 'Creation Method
                destRange.Columns(12).Value = Now()
            ElseIf testval = "" Then 'Morgan Stanley (VMware type)
                destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
                destRange.Columns(2).Value = dataRange.Columns(3).Value 'Subject
                destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
                destRange.Columns(4).Value = dataRange.Columns(8).Value 'Owner
                destRange.Columns(5).Value = dataRange.Columns(5).Value 'Status
                destRange.Columns(6).Value = dataRange.Columns(4).Value 'Product
                destRange.Columns(7).Value = dataRange.Columns(2).Value 'Product ID
                destRange.Columns(8).Value = dataRange.Columns(10).Value 'Organization
                destRange.Columns(9).Value = dataRange.Columns(9).Value 'Creation Date
                destRange.Columns(10).Value = dataRange.Columns(16).Value 'LastUpdated
                destRange.Columns(11).Value = dataRange.Columns(24).Value 'Creation Method
                destRange.Columns(12).Value = Now()
            End If
            mybook.Close
        Next
   End If
End Sub

Function ISODateToExcel(DTString) As Date
    theDate = DateSerial(Left(DTString, 5), Mid(DTString, 7, 2), Mid(DTString, , 2))
    theTime = TimeSerial(Mid(DTString, 12, 2), Mid(DTString, 15, 2), Mid(DTString, 18, 2))
    ISODateToExcel = theDate + theTime
End Function
Open in new window
GrahamSkan
You've missed out the starting position in the third Mid function
GrahamSkan
Sorry. The first is a Left, so it's the second Mid.
AckeemK
ASKER
I've added a line to each of the if statements to include a column labeled 'Product ID'. Not sure how to correct the starting position now.
ASKER CERTIFIED SOLUTION
GrahamSkan
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial