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

CLCoop

Board Regular
Joined
May 30, 2018
Messages
56
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Upvote 0
What's this?
Code:
ExcelImport.ImportExcelSpreadsheet
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
I can't find any documentation on this: ExcelImport.ImportExcelSpreadsheet. Are you sure it works? What is your reference?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top