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

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

Well-known Member
Joined
Jun 17, 2014
Messages
1,820
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.
 
Last edited:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
What's this?
Code:
ExcelImport.ImportExcelSpreadsheet
 

CLCoop

New Member
Joined
May 30, 2018
Messages
41
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,809
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
I can't find any documentation on this: ExcelImport.ImportExcelSpreadsheet. Are you sure it works? What is your reference?
 

Forum statistics

Threads
1,085,905
Messages
5,386,694
Members
402,011
Latest member
yousraemara

Some videos you may like

This Week's Hot Topics

Top