Access Import from Excel Overwrite

I am trying to import excel files from a specified folder into an Access database on a daily basis to update the tables. I found some code online that does the trick, however, it imports the new data to the table but instead of overwriting the data in the table it adds the data to the table so with each import it adds another copy of all the data. Below is the code I am using, I have been searching online for a solution but haven't had any luck... All help is appreciated. Thank you!

Sub Import_Excel_Archive_All()
'Import Data from All Worksheets in All EXCEL Files in a single Folder into Separate Tables via TransferSpreadsheet (VBA)
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String
' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:MyFolder with the actual path to the folder that holds the EXCEL files
strPath = "C:UsersScottDocumentsMy DropboxFundies DataBaseFundies Archives Daily Update"
' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = vbNullString
blnReadOnly = True ' open EXCEL file in read-only mode
strFile = Dir(strPath & "*.xlsx")
intWorkbookCounter = 0
Do While strFile ""
intWorkbookCounter = intWorkbookCounter + 1
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
blnReadOnly, , strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount
' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing

' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & colWorksheets(lngCount), _
strPath & strFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"

Next lngCount
' Delete the collection
Set colWorksheets = Nothing
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPath & strFile
strFile = Dir()
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub

Post your answer or comment

comments powered by Disqus
I am importing 1,500 records into my access program from excel and I recieved the following error

An error occurred trying to import file 'C:...09.xls'. The file was not imported.

Thank you in advance for all of your help!


I am coming up with errors when I'm importing from Excel
to Access. The error message is "field truncation." The
way I saved my Excel Sheet is in Tab Delimited so that
all my data comes through, otherwise, most of my data is
eliminated. What do I need to do so that all my data
comes through and I receive no more errors? All
suggestions appreciated.

I've created various tables in access,to store some data that will be added later with forms or imported from excel files.
the excel file I'm working with have several sheets, and repeated columns between themselves, so i created more one or two tables in order to store the data in only one place.
the problem is that i can't find the way to import "automatically" data from that sheets specifically in to the columns i want.
I'm gonna give an example to be easier: the sheets in excel have for example an entity column and other columns with entity data such as addresses, phone, that are common for several sheets of that file so i created an entity table in access DB in order to reduce the redundancy of the data in the DB and thus its size, but i don't know how to import just that columns to the entity table in access DB... Can someone help me?

I can take information from an Excel worksheet and import it into Access by specifying a path to an existing table and using DBS.AddNew to add a new record. I update the table DBS.Update and save the Primary key value to a variable which I return to the worksheet and add as a record number. intRecNum

I want to update the Access table periodically. I would like to go back to the Access Table from Excel ues the intRecNum value to pull up the specific record. I have looked for the info on Access Objects & Properties, but haven't found anything that works.

I think I need to replace DBS.AddNew something to point to an existing Record.... like DBS.ModifyRecord ?

I was thinking it would be something like :
DBS.Open "tblTest", ADOC, adOpenKeyset,
and then FindRecord.intRecNum (intRecNum being the Primary Key value I returned to Excel when I created the record.

I would like to know what command I would use to replace the DBS.AddNew and I would like to know the Object.Property I would use to access a specific Record in my table.

With my humble thanks,

We are setting up automatic importing of data from excel sheets to access database. I want to code something in access that at the click of a button will find the correct spreadsheet (excel sheets will be numbered corresponding to the current Access record), will append data from specific cells in the excel sheet to specific columns in an access table.
How do you import from excel to access specifying by cell etc?

Dear all,
Please advise on what is the best way to import Excel file into Access database. I have a Excel file that need to update everyday and import to access database(by using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9) method. The problem i have is:
1) There is always empty records imported from the Excel file into the table.
2) Sometime Access will detected extra colume whereby is invisible in Excel file.

Please advise any good solution or any other method i can take in order to solve this import from excel problem?

Thanks in advance.


Is it possible to execute a Macro on an Access Database from Excel 2007?

If so, could someone advise me on how to do this?

Thanks in advance for your help.


ps. I don't know any VB Script, so please dumb down your response, if possible!

How would I open an Access form from Excel...
using vba? This opens up an Access window but dosen't open the form:

Private Sub openaccessform_Click()

Dim ac As Object
Dim str As String
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
'ac.OpenCurrentDatabase "C:UsersCharlesDesktopPDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
ac.OpenCurrentDatabase "C:UsersCharlesDesktopPDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
End Sub

The name of the Access table is PDOXDB' the original name of the Access form I want to open from Excel is FORMVIEW
I renamed FORMVIEW in Access to AutoExec. This opens the form immediately when you click the table in Access.

I just want to open the Access form from Excel code and hide the table view some way

Thx for anyone's help.


Hi All,

Im trying to run an access query from excel which takes parameter inputs.

I'm using the code from this link modified for my DB

My problem is the query pulls data from an SQL server connection which prompts for User ID and password the first time its run. When I run the code as is, it gives an error 'ODBC Connection to RegionalDB failed'
I'm suspecting its not getting passed the login prompt.
How do I send the UserID and password automatically?

Can anyone tell me how I can run an Access sub from Excel VBA?

The sub is:

	Sub CloseDB()
End Sub

Thanks in advance


Can someone advise how to import from excel. include the heading.


hello, i used an import from excel to create a new table. every week my excel spreadsheet gets updated with monthly totals for over 8000 accounts. i try and do an append import but its not bringing in the newest information. im new at this so maybe im doing it wrong??? any idea would be nice


I wrote some code so the user could push a button and import from excel, the problem is that my unique ID for my data is an autonumber and the data imported is not getting assigned one. How can I fix that?

DoCmd.TransferSpreadsheet transfertype:=acImport, TableName:=acTable, FileName:=Me.Text1, HasFieldNames:=True

Hi there,

I'm trying to create a button on a form that will allow my database user to import from excel by browsing. I'm currently using the code below which allows them to import providing the file is in the right place with the right name

Dim stMessageBox As String

stMessageBox = MsgBox("Do you wish to update the records with a new spreadsheet", vbYesNo, "Import?")

If stMessageBox = vbYes Then

Me.LastUpdated = Now
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "AUTUMN 06", "P:foldernamefilename.xls", True, ""
Exit Sub
End If

If stMessageBox = vbNo Then
Exit Sub
End If

Does anyone know of a way I can do the same but open a browser window and choose the Excel file? Any help is appreciated.



Hi !

How I can remove empty rows when I import from excel ?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"merkinnät", "thya.XLS", True, ""



I'm running into problems with trying to create a macro and I was needing some advise... I think it would be better if it was done in VBA, but I'm not familiar with VBA... any help would be appreciated!

I'm trying to create a macro that will do the following...

1. Prompt the user to browse for an excel file.
I could use the "Run Command" -> "Import", but I'm trying to automate this process to keep the user interaction down.

2. Import the excel file (2 worksheets) into 2 table in access. The worksheets first row is a header row.
This is easy to do using the "Import" command to bring up the Import Wizard, but since I want to stay away from the wizard, I believe that I should use the "Transfer Spreadsheet" command". The problem I run into here, is that I'm required to put the filename and path into the "File Name" property. I am not sure how to set this up since I want to user to browse for the file.

Should I use VBA or can i do this with macros? Are there better commands to use or a better method to do this?
If i have to use VBA, can anyone help me or give me some further advice...

Thanks for any help you can offer.


I have many files which also contains different sheets to import from Excel into Access database. I have written a macro below to import the file into Access.

	Private Sub Import_Click()

Dim filenames As String
Dim sheet_names As String

filenames = Combo4.Value
sheet_names = Combo6.Value

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 8, "filenames", "H:Project3filenames.xls", False, "sheet_names!A10:H100"
    ' delete table data
    DoCmd.OpenQuery "filenames Query", acNormal, acEdit
    ' import data
    DoCmd.TransferSpreadsheet acImport, 8, "filenames", "H:Project3filenames.xls", False, "sheet_names!A10:H100"
    ' show data
    DoCmd.OpenTable "filenames", acNormal, acReadOnly

End Sub

With reference to the code,
The combo4 (which is the different file names) refers to the drop down menu of a form in Access.
The combo6 (which is the different sheet names) refers to the drop down menu of the same form in Access.
From the drop down menus, I can choose filenames like fruits and sheet names like apple, banana. So when I click the import button, it will import the particular file that is chosen and show the table.

The problem is the directory (H:Project3filenames.xls) and the sheet names cannot be changed. For instance if I choose fruits from the drop down menu, then the directory shd be (H:Project3fruits.xls) and if I choose apple as the sheet name, it shd be “apple!A10:H100”.

How can I have 2 user-input values (from the drop down menus which contains 2 lists of filenames and sheet names) so that whenever I click the import button, it will import the file that the user has chosen from Excel into Access?

We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option but to have data collected in Excel and for Access to the the ultimate repository for it all.

Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that i will wpare you reading).

My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need to either import the worksheets into Access as tables or to link the worksheets.

With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people who have done both or either of these methods. I have used both in the past for more limited scope tasks.

At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the field.
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give my second workbook the same file name as when I set up the links and update the links so they now show the data from the second workbook. Then run the append queries again to get this second set of data into Access. And continue with however many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update the data in the linked tables in Access.

So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the process.

And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful comments about the two processes envisioned above and any other issues I may be missing.

Thank you in advance.


I am new to using Access 2007 and i would appreciate help regarding importing data from excel to Access.

My Excel spreadsheet's format is as follows:

Name Group NO SSN Name 1 1 SSN Number Name 2 1 SSN Number Name 3 1 SSN Number Name 4 2 SSN Number Name 5 2 SSN Number Name 6 3 SSN Number Name 7 4 SSN Number and so on...

My Access table format is as follows (how i would like my information to appear on Access):

Group NO (Primary Key Autonumber) 1-Name 1-SSN 2-Name 2-SSN 3-Name 3-SSN 1 Name1 (SSN Number) Name 2 (SSN Number) Name 3 (SSN Number) 2 Name4 (SSN Number) Name 5 (SSN Number) 3 Name6 (SSN Number) 4 Name7 (SSN Number)
How can i import the information So that the group number from the excel sheet corresponds to the record number on the Access Table. Also, listing out the Name and their SSN to the corresponding columns for each group number.
Any help would be greatly appreciated and thank you for lending me your knowledge.

Hi, I need to import data from Excel into a table in Access and I need to be able to look at the table and readily identify the records I just imported. If Access would import the cell color for the new records, that would be great but I don't know how to make Access do that. Is there a way or can anyopne offer another solution?
Thanks in advance for any help,

I am having problems importing data from excel to Access using the Wizard. It use to work but does'nt anymore. I don't know if anyone has encounted a similar problem and how they resolved it.

After I specify the "Source of Data" and check radio button "Import the source data into new table in current database" and then click OK, nothing happens. No error message. Nothing!!

Any help or suggestions or alternatives.


Hi fellows, I am working on a material management system. There is a table which only includes material codes and descriptions as a primary key and there is another table for data entries connected to the material list table with one-to-many relation ship. On data entry table users are importing packing lists from excel with transferspreadsheet macro. If material is not in the packing list user suppose to add this material to the mat. list table. But its very likely to miss those items which are in the packing list and are not in the mat list table.In such a case access will import packing list without these items. Therefore I would like to put a control mechanism which gives a warning to the user if there is additional material which was not defined in the mat. list before so user can add it.

Hi All,

Please can someone help with this:

I have found the following code:

Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.hwnd
'OpenFile.hInstance = App.hInstance
sFilter = "acSpreadsheetTypeExcel8 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "P:BevWork InChange Control"
OpenFile.lpstrTitle = "Use the Comdlg API not the OCX"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "The User pressed the Cancel Button"
MsgBox "The user Chose " & Trim(OpenFile.lpstrFile)
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).Name
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, "Testing Tab", OpenFile.lpstrFile, False, "Access"
Next i
End With
End With
Set oApp = Nothing
End Sub

This I have attached to a button and it works great except for the part where it is importing the data from excel range 3 times!!! Any ideas why.

It is also opening the spreadsheet selected which ideally i dont want it to do. And on top of that when I close the spreadsheet manually, excel then tries to open it again giving the usual 'workbook is now available to open' message and then locks up as I try to press cancel.

I'm a newbie to VBA, so can't spot where it's going wrong. Any help is appreciated as it's driving me crazy!!!



I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.

I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.

I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.

Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.

I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.

my worksheet is in the following format

ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
FD 7 2011 15 .................... 987 7/15/2011
SM 7 2011 15 .................... 632 7/15/2011
KW 7 2011 15 .................... 838 7/15/2011

All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)

Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)

thank you very much.

FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.

Sub Import_multiple_excel_files()

Const strPath As String = "C:Price DataNYMEX2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")

While strFile ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Not finding an answer? Try a Google search.