I have several pages where user upload excel files for importing records into a database.
I detect the format and open the connection string appropriately like this.
If FileUpload1.HasFile Then
strExt = Right(FileUpload1.FileName, (Len(FileUpload1.FileName) - InStrRev(FileUpload1.FileName, ".")) + 1)
If strExt = ".xls" Or strExt = ".xlsx" Then
Dim strLocalPath As String
strLocalPath = DLookUp("ParamValue", "tblSysParam", "ParamName = 'TempFileDir'")
txtPath.Text = strLocalPath
strFileName = ExactTimeString() & strExt
txtFile.Text = strFileName
FileUpload1.SaveAs(strLocalPath & strFileName)
If strExt = ".xls" Then
strC = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & strLocalPath & strFileName & "';Extended Properties=Excel 8.0"
ElseIf strExt = ".xlsx" Then
strC = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strLocalPath & strFileName & "';Extended Properties=Excel 12.0"
txtConn.Text = strC
DisplayAlert("File of type " & strExt & " not permitted only .xls or .xlsx maybe uploaded.", Me.Page)
DisplayAlert("No sample information detected.\n\nIf you believe this to be an error please contact your system administrator.", Me.Page)
myConnection.ConnectionString = strC
I know my code can handle 2007 format as I have sucessfully uploaded some. However, when I upload a large file (30MB) I get the external table is not in correct format error.
What is really weird is if I upload the same file in excel 2003 format it works. So I know it is not the maxupload size that is the problem. I had read that the permissions of the temporaryinternbet files folder could be an issue but then why would it work with 2003?
If I remove a large portion of the data from the 2007 version of the file (leaving the number of columns in tact) the connection works so I must be hitting a size threshold but I can't figure out what setting I need to change.
I am totally stumped here. Can anyone help?