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

CLCoop

New Member
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
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:

CLCoop

New Member
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
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
I can't find any documentation on this: ExcelImport.ImportExcelSpreadsheet. Are you sure it works? What is your reference?
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top