gctools/excel/split.xls.vb

Sub Workflow(sourceFile As String)
    Dim Current As Worksheet
    Dim targetFile As String
    Dim fileFormat As Integer
    Dim ext As String
        
    Workbooks.Open Filename:=sourceFile
    If Right(sourceFile, 3) = "xls" Then
        sourceFile = Left(sourceFile, Len(sourceFile) - 4)
        fileFormat = XlFileFormat.xlExcel7
        ext = ".xls"
    Else
        sourceFile = Left(sourceFile, Len(sourceFile) - 5)
        fileFormat = 51 ' XlFileFormat.xlOpenXMLWorkbook
        ext = ".xlsx"
    End If
    
    For Each Current In Worksheets
        Current.Select
        targetFile = sourceFile + "_" + Current.Name + ext
        Current.Copy

        ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
        ActiveWorkbook.SaveAs Filename:=targetFile, fileFormat:=fileFormat, CreateBackup:=False
        ActiveWorkbook.Close
    Next
    ActiveWorkbook.Close
    Application.Quit
End Sub