Whether xlsx extension works while importing data sheet?

[cflow]

I required this for my testing today, so here is a fully functional XLSX importer

Disclaimer: This does not format or cleanup the Excel sheets themselves, just renames them for import so they are CamelCase. If you want your columns in the datasheets to be CamelCase with no special characters you will need to do some further extension.

Code:

'Usage for XLS or XLSX:
Dim filePath
filePath = "C:\File\Path\Goes\Here\FileName.xlsx"
importAnyXL filePath

Function importAnyXL(filePath)
   Dim xl, xlFile 'Objects
   Dim FileFormatNum, iSheetCount, a, i 'Integers
   Dim newFilePath, sheetName, cleanSheetName 'Strings
   Dim fileDelete 'Boolean
   Dim sheetList()  'Array
        Set xl = CreateObject("Excel.Application")
        xl.DisplayAlerts = False
        Set xlFile = xl.Workbooks.Open(filePath)
        iSheetCount = xlFile.Sheets.Count
        For a = 0 To (iSheetCount - 1)
            ReDim Preserve sheetList(a)
            sheetList(a) = xlFile.Sheets(a + 1).Name
        Next
        If UCase(Right(Trim(filePath), 4)) = "XLSX" Or UCase(Right(Trim(filePath), 4)) = "XLSM" Then 'Dont forget to cater for macro enabled files ;)
            FileFormatNum = -4143
            filePath = Left(filePath, Len(filePath) - 5) & "_Temp.xls"
            xlFile.SaveAs filePath, FileFormatNum
            fileDelete = True
        End If
        xlFile.Close False
        xl.DisplayAlerts = True
        xl.Quit
        Set xl = Nothing
        For Each sheetName In sheetList
            cleanSheetName = camelCase(sheetName)
            DataTable.AddSheet camelCase(sheetName)
            DataTable.ImportSheet filePath, sheetName, cleanSheetName
        Next
        'Clean-up after yourself :)
        If fileDelete Then
            Dim FSO
            Set FSO = CreateObject("Scripting.FileSystemObject")
            If FSO.FileExists(filePath) Then
               FSO.DeleteFile filePath
            End If
            Set FSO = Nothing
        End If
End Function

And for a Bonus

Code:

Function camelCase(strIn)
   Dim strOut, boolUp, i, c, tc
    strOut = ""
    boolUp = True
        For i = 1 To Len(strIn)
            c = Mid(strIn, i, 1)
            If c = " " Or c = "'" Or c = "-" Then
                strOut = strOut & c
                boolUp = True
            Else
                If boolUp Then
                    tc = UCase(c)
                Else
                    tc = LCase(c)
                End If
                strOut = strOut & tc
                boolUp = False
            End If
        Next
    camelCase = Replace(Replace(strOut, " ", ""), ".*", "")
End Function