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

AckeemKAsked:
Who is Participating?
 
GrahamSkanRetiredCommented:
In this function,
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 Functio

Open in new window

the second parameter in the function:
Mid(DTString, , 2)

Open in new window

is not specified.
The Mid function takes the source string as the first parameter, the starting position within that string as the second and the number of characters to be returned as the third parameter. You can omit the third parameter, in which case the function returns the string from the starting position the the end, but you can't omit the second parameter as we don't know where to start.
0
 
GrahamSkanRetiredCommented:
You've missed out the starting position in the third Mid function
0
 
GrahamSkanRetiredCommented:
Sorry. The first is a Left, so it's the second Mid.
0
 
AckeemKAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.