Dropping last field on excel imported worksheet

[ssalem]

I am having an issue with an Access import. It worked correctly bringing in a sharepoint spreadsheet until a date field was added to the spreadsheet and the structure to the database. Now it drops any additional fields that are added. At first I thought if I added a filler field after the field I needed that it would still drop the last field but it dropped them both. If I insert the field in the middle of the spreadsheet....(where I really want it). It drops the last field of the email address. Which I need to send out notifications. I have tried designing a complete new table and importing it, but it continues to do the same thing.

Not sure if my problem is with the spreadsheet or Access. Somehow it is still looking at the old structure when I import even if I create a new table. Not sure what is happening.

I have tried researching online about what is happening but have not been able to find anything. I found one instance but it was not resolved.



I am somewhat a novice and most of the projects I work on I do alot of research.

[ssalem]

On further note....I created a whole new database. When I import the spreadsheet in through the import function as an excel spreadsheet it works. This is the code in my module for importing data. Don't understand why it is dropping the last field of my spreadsheet.

Public Function ClearTableImport()
Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next: db.TableDefs.Delete "tlbImports": On Error GoTo 0
db.TableDefs.Refresh
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE PAFS2.* FROM PAFS2;"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="PAFS2", _
FileName:="http://xxx.com/xx/xxxx/xxx.xlsx", _
HasFieldNames:=True, _
Range:="Worksheet name!a1:t100"
db.TableDefs.Refresh