Excel Import to Access Dropping Records

[uncled]

Hi All - I've got a weird problem here that I couldn't find in another thread. I'm importing and Excel file into an existing Access table using a command button on a form. My code (as follows), delete all records from the table then imports the excel records.

cmdCommand.CommandText = "delete * from Opportunities"
cmdCommand.Execute

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Opportunities", sFileLocation, True, "A2:X5000"

PROBLEM: the Excel file is created from a download from PSFT CRM system. If I upload the file immediately using the above code, it is only importing a portion of the records. However, if I open the Excel File and then Save (not Save As) and them run the code, everything imports ok.

Everything looks ok with the Excel file (i.e. it's being saves as an Excel Worksheet when exported from our CRM), so don't know what's happening to the file when I open and save.

My thought on fixing/automating: in the code, open and save the Excel file and then import. I'm a fairly novice user and not sure exactly how to call the Excel file and Save it. Any help would be appreciated as to this solution or any other solution.

thanks,
-Ed

 

[uncled]

I think I found a solution (I don't necessarily like it however). I found forum that discussed this and provided a solution. I incorporated some of this and my code is as follows:

cmdCommand.CommandText = "delete * from Opportunities"
cmdCommand.Execute

Set openXL = CreateObject("Excel.Application")
openXL.Workbooks.Open sFileLocation
openXL.Visible = False
openXL.ActiveWorkbook.Close True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Opportunities", sFileLocation, True, "A2:X5000"


I'm basically opening the Excel file and Saving it all in the background. Don't care for this solution, but couldn't figure out another way. Does anyone see any problems I may run into? I'm a bit nervous that this may have some effect on any other open Excel files I may have at the time this is run, though I tested and it seemed ok.

-ed