appending an Excel worksheet to an existing Access database

How can I with the code below when the database opens and table2 already exist delete it and make a new table2?

On Error GoTo errHandler
Dim mExcelFile As String
Dim mAccessFile As String
Dim mWorkSheet As String
Dim mTableName As String
Dim mDataBase As Database
mExcelFile = App.Path & "Book1.xls"
mAccessFile = App.Path & "Db2.mdb"

mWorkSheet = "Sheet1"
mTableName = "Table2"
' Below you may use "Excel 7.0" or 8.0 depending on your installable ISAM.
Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
" FROM [" & mWorkSheet & "$]"
MsgBox "Done. Use Access to view " & mTableName
Exit Sub
If Err.Number = 3010 Then
MsgBox mTableName & " already exist." & vbCrLf & _
"Delete " & mTableName & " first or use another table name."
MsgBox Err.Number & " " & Err.Description
End If

Sponsored Links:

Hi, All,

Appending an Excel file to an Access table

Just now I tried to convert an Excel file (let's call it source_1.xls) into an Access database (Access 2000). The first line of the Excel file represents the field names in the database.

I opened the Access program, clicked on 'Blank Access database', named the desired Access file, and saved it as fred.mdb

I clicked file | get external data | import |
and selected source_1.xls, then clicked import

The following unhelpful since unspecific error msg came up:
"Wizard unable to access information in file xyz. Check that file exists and is in correct format."

Can anyone please tell me what to do?


A puzzling aspect of this affair is the following:

A couple of weeks ago when first trying to make this conversion from Excel into an empty database I did succeed, but unfortunately it was so easy that I did not see the need for writing down what exactly I did, and now I cannot reproduce it.

But the files left over from that experiment are puzzling and may shed light on my current problems.

For safety reasons I made, at that time, a copy of file source_1.xls (let's call it source_2.xls) and renamed it. Looking at the two source files I can see no difference between them. Both have the same number of records and fields. Both have the field names in the first row.

Now the miracle:
1 source_2.xls (88 kb) is almost exactly twice as large as source_1.xls (43 kb).
2 source_2.xls imports into Access OK without the above error msg.

What on earth could I have done to the Excel file to double it in size and to make it acceptable to Access for import?

Could it have something to do with delimiters that I have forgotten?


Now to the overall context of this problem.

I regularly receive a fairly small Excel file (always in the same format) with addresses and I have to append these addresses to my Access database without fuss and error. Esp. I must avoid records becoming scrambled up (e.g. wrong name joined with wrong address by risky pasting into tables).

The file mentioned above is the first Excel file that arrived. I converted it into Access (but see problems mentioned above), then deleted a number of fields and added a number of others.

I took care not to alter the names of the fields which the Excel table and the Access table are intended to have in common.

In future (once or twice a month) I want to import the future Excel files into the existing database, by appending them. I expect Access to import, for each record, the matching fields in the Excel file, and ignore the non-matching fields (i.e. those which exist only in Excel or only in Access).

btw: It is ***not*** practicable for me to expand the Excel table manually to match the Access table.

Question: I there a utility in Access which could accomplish this.

Or: Is there a stand-alone utility (not too expensive) which I could use to convert the Excel table into a common format from which I can easily import it into Access in the way described.

What else can I do to accomplish what I need?

I have a voluminous book, 'Running MS Access 2000' by John Viescas, but do not know where to look for what I want (if it exists). Please refer me to a page if the answer is there?

If there is a Tutorial on the Internet which describes my specific problem, please refer me to it.

Many thanks for your help.


Hello Access experts,

I have been touring around the web looking for some decent answer to my problem until i bumped into this cool website that might help me.

The attached file contains an excel spreadsheet that has information on it that i want to import to my access database specifically on column "C". Importing a file from excel was easy when the data you want to get are arranged in a Row but in this case it is arranged in a Column.

Can anyone help me with an Access Macro that can Automate the import?

1. Command button allows me to pick the excel file and automatically imports to my access database and everytime i import another file, it appends to the database.

This is really challenging and hope anyone can help me

This code works perfectly to append one excel file but I need help to amend it so it can append one file after another. Any ideas?

Quote: Option Compare Database
Option Explicit

Const constColumns = 53
Const constFirstLine = 17

' Tracker Fields
Const constRef = 1
Const constDate_of_Reg = constRef + 1
Const constCompany = constRef + 2
Const constContractor = constRef + 3
Const constDirectorate = constRef + 4
Const constProgram_Project = constRef + 5
Const constContract_Descr = constRef + 6
Const constSupply_Service_or_Works = constRef + 7
Const constForm_of_Contract = constRef + 8
Const constTotal_Value = constForm_of_Contract + 1
Const constContract_and_variation_no = constForm_of_Contract + 3
Const constAgreed_Award_Date = constTotal_Value + 4
Const constPeriod_of_Award_Date = constTotal_Value + 5
Const constCustomer_GM = constAgreed_Award_Date + 5
Const constCustomer_BM = constCustomer_GM + 1
Const constGM_Area = constCustomer_BM + 1
Const constGM = constCustomer_BM + 3
Const constBM = constCustomer_BM + 4
Const constProc_Contact = constCustomer_BM + 5
Const constLegal_Contact = constCustomer_BM + 6
Const constCurrent_Status = constLegal_Contact + 1
Const constReason_Code = constLegal_Contact + 3
Const constNext_Management_Steps = constLegal_Contact + 4
Const constActual_Contract_date = constNext_Management_Steps + 11
Const constPeriod_Contract_date = constNext_Management_Steps + 12
Const constStatus = constNext_Management_Steps + 14

Dim appExcel As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object

Dim db As Database
Dim rs As Recordset

Public Sub basControl_Import()

' controlling subroutine for import Excel information
If basOpenRecordset() Then
If basOpenExcel _
("Z: ProcurementPerformanceContracts.xls") _
End If
End If

End Sub

Public Function basOpenRecordset() As Boolean

On Error GoTo basOpenRecordset_err

' Empty the table used to hold the imported information
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblLive_Tracker"
DoCmd.SetWarnings True

' open the table in preperation of importing the data
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblLive_Tracker", DB_OPEN_DYNASET)

On Error Resume Next
basOpenRecordset = True
Exit Function

MsgBox "Error clearing and then opening tblLive Tracker : " & Error(Err)
basOpenRecordset = False
Resume basOpenRecordset_ok

End Function

Public Function basOpenExcel(strFile As String) As Boolean

Dim intI As Integer
Dim intJ As Integer

'Debug.Print "Start: " & Now()
' open Excel as an object
On Error GoTo basOpenExcel_err
Set appExcel = CreateObject("Excel.Application")

' Open the workbook
Set xlBook = appExcel.Workbooks.Open(strFile)
'Open the first worksheet
Set xlSheet = xlBook.Sheets("TRACKER")

' Set the start point
intI = constFirstLine

' Keep reading lines until the first cell on a line contains an empty string
While Len(xlSheet.Cells(intI, 1)) 0
'Debug.Print intI
' Check to see if this line is a recognised data line
If basDataline(intI) Then
' Pull in the fields and add to the recordset
basAddLine (intI)
End If
' Next row
intI = intI + 1

basOpenExcel = True

' Exit routine
On Error Resume Next
' Close the workbook (no changes to save)
xlBook.Close SaveChanges:=False
' Quit Excel
'Debug.Print "Stop: " & Now()
Exit Function

' Error handling
MsgBox "Error opening Excel workbook " & strFile & " worksheet Master Tracker : " & Error(Err)
basOpenExcel = False
Resume basOpenExcel_ok

End Function

Public Function basDataline(intRow As Integer) As Boolean

' Specific test to see if this row has data that we want to import
If xlSheet.Cells(intRow, 1) "Overall Result" Then
basDataline = True
basDataline = False
End If

End Function

Public Sub basAddLine(intLine As Integer)
'Dim tmpDate_12Months As Date
'Dim tmpDate_09Months As Date

DoCmd.SetWarnings True
On Error GoTo basAddline_err

' Create a row in the recordset
' Update the fields with the specific data, each field is a specific type)
rs![Ref] = basGetTextField(intLine, constRef)
rs![Date_of_Reg] = basGetDateField(intLine, constDate_of_Reg)
rs![Contractor] = basGetTextField(intLine, constContractor)
rs![Company] = basGetTextField(intLine, constCompany)
rs![Directorate] = basGetTextField(intLine, constDirectorate)
rs![Program_Project] = basGetTextField(intLine, constProgram_Project)
rs![Contract_Descr] = basGetTextField(intLine, constContract_Descr)
rs![Supply_Service_or_Works] = basGetTextField(intLine, constSupply_Service_or_Works)
rs![Form_of_Contract] = basGetTextField(intLine, constForm_of_Contract)
rs![Total_Value] = basGetNumericField(intLine, constTotal_Value)
rs![Agreed_Award_Date] = basGetDateField(intLine, constAgreed_Award_Date)
rs![Period_of_Agreed_date] = basGetTextField(intLine, constPeriod_of_Award_Date)
rs![Customer_GM] = basGetTextField(intLine, constCustomer_GM)
rs![Customer_BM] = basGetTextField(intLine, constCustomer_BM)
rs![Proc_GM] = basGetTextField(intLine, constGM)
rs![Proc_BM] = basGetTextField(intLine, constBM)
rs![Proc_Contact] = basGetTextField(intLine, constProc_Contact)
rs![Legal_Contact] = basGetTextField(intLine, constLegal_Contact)
rs![Current_Status] = basGetTextField(intLine, constCurrent_Status)
rs![Reason_Code] = basGetTextField(intLine, constReason_Code)
rs![Next_Managment_Steps] = basGetTextField(intLine, constNext_Management_Steps)
rs![Actual_Contract_date] = basGetDateField(intLine, constActual_Contract_date)
rs![Period_contract_date] = basGetTextField(intLine, constPeriod_Contract_date)
rs![Status] = basGetTextField(intLine, constStatus)
rs![Contract_and_variation_no] = basGetTextField(intLine, constContract_and_variation_no)
rs![Proc_Area] = basGetTextField(intLine, constGM_Area)

'tmpDate_12Months = DateAdd("m", -12, Date)
'tmpDate_09Months = DateAdd("m", -9, Date)
'rs![agencyLengthOfService] = IIf(rs![agencyDES] < tmpDate_12Months, 12, IIf(rs!agencyDES < tmpDate_09Months, 9, 0))


DoCmd.SetWarnings False

Exit Sub

MsgBox "Error appending line to tblLive Tracker line [" & intLine & "] : " & Error(Err)
Resume basAddLine_ok

End Sub

Public Function basGetTextField(intLine, intCol) As String

' Text field
On Error GoTo basGetTextField_err

basGetTextField = xlSheet.Cells(intLine, intCol)

On Error Resume Next
Exit Function


MsgBox "Error importing text from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetTextField = ""
Resume basGetTextField_ok

End Function

Public Function basGetNumericField(intLine, intCol) As Long

' Numeric field
On Error GoTo basGetNumericField_err

basGetNumericField = xlSheet.Cells(intLine, intCol)

On Error Resume Next
Exit Function

MsgBox "Error importing numeric from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetNumericField = 0
Resume basGetNumericField_ok

End Function

Public Function basGetDateField(intLine, intCol) As Date
' Date field
On Error GoTo basGetdateField_err

basGetDateField = xlSheet.Cells(intLine, intCol)

On Error Resume Next
Exit Function

'MsgBox "Error importing date from row " & intLine & " column " & intCol & " " & xlSheet.Cells(intLine, intCol)
basGetDateField = #1/1/1900#
Resume basGetdateField_ok

End Function


I'm sure this is a very straightforward question, but I can't think of the answer. I am using a macro to import an Excel spreadsheet to an existing table in an Access 97 database (yes, we still use 97 in the office!). If I import the table manually, there is no problem and when I open the table, there are the expected number of rows (in this case 25724). However, if I use the macro, only 17413 rows are imported. I've tried removing the PK just in case (but as it works when I import manually, I didn't expect that to help (and it didn't). I've checked the imported data against the whole spreadsheet, and I can't see anything apparent in the rows that have failed to import. Can anyone help?

(I've reduced the macro to just that one function so I know the problem is somewhere in the TransferSpreadsheet action).

Many thanks


I need help.
I am a new access user and am trying to create a simple claims database.
I have created all the tables that I need so far , but I need one more table that will house the answers to an excel macro.

This Macro is a series of questions that a customer is asked at enrollment time and the answers determine if the customer is accepted or not accepted in the program.

I tried linking the worksheet to my database what I got was the macro programming rather than the answers and final decision.
What I want ideally is only the answers to the questions in this table, I don't need to see all the options available in the macro.
I don't know if my explanation makes sense , but any help is greatly appreciated.

Thank you

Automate the transfer of multiple excel files to one Access table.

Hello. I'm new to this forum and whilst not a complete newbie to Access or excel or basic VBA programming i didn't have a clue how to do this but i did find some code when searching for answers through google
However, i want to do two things in addition to what the code below does. I want to transfer these files but my records start on a certain row in excel. To Be exact row 17. I also want to be able move the imported excel files into an exported folder and exporting

I hope someone can give me some advise on how to do this


Here is the code i found

Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False

' Replace C:Documents with the real path to the folder that
' contains the EXCEL files
strPath = "C:UsersKevinGoogle DriveATOSATOS Reports"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()

End Function

I have two queries which are data sources for an Excel worksheet...

My problem: If I try to refresh the Excel file when the database is OPEN, I get an error msg saying "The database has been placed in a locked state...blah blah"

[I'm running Access 02 - never had this problem in previous versions]

VBA (command button) opens the Excel file externally through Access after going through the necessary computation.

As an alternative, I could create a (new) workbook object each time through Access, but then I cannot use the graphs that I want to build and refresh in my Excel file.

Any suggestions?

Hi everyone,

I have an error to import an excel file into ms access 2.0(1993). I am developig a vb6 application.Back end is ms access 2.0. 5000 records are saved in the excel sheet. I need to import to access 2.0. I am using ms access 2003. I could not edit or modify the 2.0 database in the ms access 2003. And also, I could not import the excel file into ms access 2.0.

Please guide me.

Thanks in advance,

J. Mohan

Hi I am trying to add more data points to an existing system that is importing data from Rockwell PLC Rslogix5000 to Microsoft Access Database, but I could not find out what type of communication link between the MS Access Database and the PLC. Could anyone tell me how to find that?

How to embed a Microsoft excel file into Microsoft access database.

I came across this strange error for the first time. I am trying to export all my access database objects (Forms, Queries, Reports, Modules) to another access database file. While some of the object will export successfully, about one or two forms and modules will not and give the following error.

File not found: 'C:UserssandaaAppDataLocalTempVB31D5.tmp' or
File not found: 'C:UserssandaaAppDataLocalTempVB4A76.tmp'

depending on the object and whenever I click OK button I get a further error report saying:

"Name COnflicts with existing module, project, or object library."

Does anybody know how I can resolve this problem?


First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.

OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.

How would I go about performing this task? I have never attempted anything in the past.

Thank you in advance for your considered help,


I was wondering if there was a way for a report to be automatically saved as an excel file. Also, the Access database is stored on the company's server so would you be able to choose the path where the excel file would be stored?


I have mastered just beyond the basics of VBA and now am trying to learn a little VB. Just for learning purposes I am trying to recreate one of my completed Access database in Visual Basic. I downloaded Visual Studio 2008 Express Edition. I realize that in VB you cannot have bound forms as you can in Access, so I want to connect my VB project to my current Access database tables. I theoretically know how I would add, access, modify, delete, etc. my data without having those bound forms. I would use (and am only assuming that this would be the right way) SQL statements and recordsets to manipulate data and populate fields, etc. So my problem is, how do I originally connect to my Access database? I found the option to "connect to Database..." under the Tools menu, and successfully connected to my Access database, but now have no clue on how to use/reference it in my code. In the left pane of my Visual Basic window I now see the Database Explorer window which lists all my tables, fields in those tables, and even what appears to be my saved queries and functions. How do I now use this in my code?
For a simple example lets just say I wanted to make a simple form that shows the Name Address and Phone number for records in my Customers table from my Access database. I would understand the code behind setting the values of the fields/controls, and could figure out the code for navigating to the previous/next records, but its just that initial connection and opening of a recordset or referencing a table in an SQL statement that I cannot figure out. BTW, can I even use recordsets and SQL in VB as I can in VBA?

I am tryin very hard to link an Access table form one Access Database to another Access Database. Does anyone have any idea how to do this?

I am trying to do this by linking to the table through the Access interface. I click "link tables", then select an "access database type". I then "map a network drive" from the tools menu, then select "web folder or FTP site". I enter in my IP address e.g. and it says that this is not valid.


I linked an excel spreadsheet to my access database and created a report that uses that spreadsheet. The problem that I'm having is that if I have the excel spreadsheet open and another user on a different computer opens up the access report, the linked excel spreadsheet will open up as well. How do I stop this from happening?

How do I import an excel file to a table with out it being Linked? I have a data base that works off of a table that is currently linked to a excel file. This file needs to be updated through out the day with all new data each time. But if i have the database open I cannot update the excel file. Is there a way I can just pull the data from the excel file?

Hi Group
I am trying to add an outlook contact from my access database. This code is attached to the OnClick event of a command button and works fine….it adds the contact and enters in the full name. Problems start happening when I try to add additional fields within the WITH statement. I get an Error 438 message – Object doesn’t support this property or method. Does anyone have any ideas???
Thanks for your input
Private Sub AddOLContact_Click()
On Error GoTo AddAppt_Err
'Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord
'Exit the procedure if appointment has been added to Outlook
If Me!AddedToOutlook = True Then
MsgBox "This appointment already added to Microsoft Outlook"
Exit Sub
'Add a new appointment
Dim outobj As Outlook.Application
Dim outappot As Outlook.ContactItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olContactItem)
With outappt
FullName = Me.FName & " " & Me.LName

End With
End If
'Release the Outlook object variable
Set outobj = Nothing
'Set the AddedToOutlook flag, save the record, display a message
Me!AddedToOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Contact Added!"
Exit Sub
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub

End Sub

I have a macro within my access database that exports data to an excel worksheet and this works great. However i now want to add data to an "existing" excel worksheet ie one that already has data in it from a previous export from access. Can this be done, if so can anyone point me in the right direction.