You are missing an argument in the "TransferSpreadsheet" command.
You have
Code:
DoCmd.TransferSpreadsheet acImport, , "SourceTable", Filepath, True
If you want to import from an Excel 97 or 2000 format spreadsheet, try
Code:
DoCmd.TransferSpreadsheet acImport, 8, "SourceTable", Filepath, True
or
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SourceTable", Filepath, True
For Excel 2010 format, try
Code:
DoCmd.TransferSpreadsheet acImport, 10, "SourceTable", Filepath, True
or
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", Filepath, True
As for your code, I would first get the transferspreadsheet command working. (I modified your code a little)
Code:
Option Compare Database
Option Explicit
Private Sub cmdImport_Click()
Dim Filepath As String
Dim SQL As String
Me.txtImport.SetFocus
Filepath = txtImport.Text
If FileExists(Filepath) Then ' FileExists is a function to check if file exits
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", Filepath, True
DoEvents
' SQL = "INSERT INTO DestinationTable" & _
' "SELECT SourceTableField1,SourceTableField2" & _
' "FROM SourceTable" & _
' "WHERE NOT EXISTS(SELECT * FROM DestinationTable" & _
' "WHERE (SourceTable.SourceTableField1=DestinationTable.DestinationTableField1 AND SourceTable.SourceTableField2=DestinationTable.DestinationTableField2))"
'
'
' DoCmd.RunSQL SQL
Else
MsgBox "File not found. Check file name or It's location !"
End If
End Sub
See if the new table is created.
Once the new table is created with data, un-comment the SQL and execute it.