VBA CODE TO IMPORT EXCEL INTO ACCESS TABLE UPDATE and ADD TO THE TABLE

[CLCoop]

I'm trying to have access look out to a file (file name could change) then UPDATE and if not in the table ADD the information from the spreadsheet Tab ATT to the ATTORNEY table. Right now I'm struggling just to get the file to import into Access. I've created an excel file with a Tab named ATT and want it to upload in order to update and add the information to the table called ATTORNEY. The original spreadsheet (tab ATT) will grow in rows/records...

'This part of the code works (using a browser I select the file I want to use)
Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"

If diag.Show Then
For Each item In diag.SelectedItems
Me.txtfilename = item
Next
End If


End Sub
-----------------------------------------------------
'THIS IS WHERE THE PROGRAM STOPS.
Private Sub btnImportAtt_Click()
Dim FSO As New FileSystemObject

If FSO.FileExists(Me.txtfilename) Then
ExcelImport.ImportExcelSpreadsheet Me.txtfilename, FSO.GetFileName(Me.txtfilename) 'get a compile error: variable not defined"
End If

End Sub

Course the next step is once the table is imported to update and add records to the Attorney table. Can you do both or will I need to create separate code to run Update and another to add query?

Thank you for your support

 

[ranman256]

You don't need the FSO, it was already set in the 1st code.
just use me.textfilename

You don't need excel import.
Use docmd.transferspreadsheet.

 

[xenou]

What's this?

ExcelImport.ImportExcelSpreadsheet

 

[CLCoop]

This comes from the Tools, References, Microsoft Office 16.0 object library that then allows you to add the statement importexcelspreadsheet (got this form an online video) Still working on how to best import an excel that will update and add the information to a table if anyone can help that would be awesome.

Thanks

 

[Joe4]

Still working on how to best import an excel that will update and add the information to a table if anyone can help that would be awesome.

I assume that you have a unique identifier field in the file and table in which you can match on.

If so, then one way is to import it to a Temporary table, which has the exact same structure as the table you want to import it to.
Then, run two queries:
- In a query, join the the Temporary table to your final table on your unique identifier. Then change to an Update Query, updating the fields that you want.
- Create an unmatched query between the Temporary table and your final table. Then change this to an Append Query to add the new records.

 

[xenou]

I can't find any documentation on this: ExcelImport.ImportExcelSpreadsheet. Are you sure it works? What is your reference?