Excel Spreadsheet Import - Help on VBA Code

[Susy]

Hi guys,
I am trying to update an existing table with an Excel spreadsheet import, but I am struggling with one part: My codes work perfectly (print screen attached), I can import the data into a new table (highlighted in yellow), but I don't manage to change this and just actualise an existing table. Can someone please help? The table name where I would like to import the data is called "_Freight".
Thanks and regards,
Susy

 

[Ranman256]

don't cram everything into 1 sub:

Public Sub Pick1File2Import()
Dim vFile
vFile = UserPick1File()
If vFile <> "" Then
docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel9 ,"table",vFile,true,"sheetName"
endif
End Sub

Public Function UserPick1File()
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr As String, sExt As String
'msoFileDialogFilePicker = 3
'msoFileDialogViewList = 1
'getFilterTxt pvFilter, sDecr, sExt, sDialog
If IsMissing(pvPath) Then pvPath = "c:\"
With Application.FileDialog(3)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
.AllowMultiSelect = True
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx"
'.Filters.Add "Text Files", "*.csv"
'.Filters.Add "All Files", "*.*"
'.Filters.Add sDecr, sExt
.InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        If .show = 0 Then
'There is a problem
Exit Function
        End If
      'Save the first file selected
UserPick1File = Trim(.SelectedItems(1))
End With
End Function

 

[bastanu]

inside the If statement for the import add:

If FSO.FileExists(Me.txtFileName2) Then

      currentdb.Execute "DELETE * FROM _Freight;" 'THIS WILL CLEAR THE CONTENTS OF THE -freight TABLE!!!!

     ExcelImport.ImportExcelSpreadsheet(Me.txtFileName2,"_Freight")

Else

......

Cheers,
Vlad

 

[Susy]

inside the If statement for the import add:

If FSO.FileExists(Me.txtFileName2) Then

      currentdb.Execute "DELETE * FROM _Freight;" 'THIS WILL CLEAR THE CONTENTS OF THE -freight TABLE!!!!

     ExcelImport.ImportExcelSpreadsheet(Me.txtFileName2,"_Freight")

Else
......

Cheers,
Vlad

Hi Vlad,

Thanks a lot for your help.

I have tried your code, but it doesn't work:

Moreover, is there a way to simply complete the table and not clear the contents?

Thanks,
Susy

 

[arnelgp]

use Call

Call ExcelImport.ImportExcelSpreadsheet(Me.txtFileName2, "_Freight")

or

ExcelImport.ImportExcelSpreadsheet Me.txtFileName2, "_Freight"

 

[Susy]

use Call

Call ExcelImport.ImportExcelSpreadsheet(Me.txtFileName2, "_Freight")

or

ExcelImport.ImportExcelSpreadsheet Me.txtFileName2, "_Freight"

Thanks a lot. It works perfectly!

Do you have any idea how I could simply complete the data and not replace it?

Thanks,
Susy

 

[arnelgp]

you need to import the excel file to a Temp table.
use Update query to update or add new record to _Freight table from the temp table.

 

[Susy]

you need to import the excel file to a Temp table.
use Update query to update or add new record to _Freight table from the temp table.

Ah thanks a lot! This is a great idea!