Import data to Access from Excel

February 4, 2008 – 1:53 pm by JP

Back in January, I promised to post a macro that imports data from Excel into Access programmatically.

It takes a given spreadsheet and imports it into a given Access file/table. The spreadsheet must have a header row, with no blank rows or columns inside the table block. You must set a reference to the appropriate Access object library (Microsoft Access x.0 Object Library).

How it works:

First it uses a simple API call to get the local username, so that the spreadsheet can be (temporarily) saved to the user's desktop. Then it checks to see if the .mdb file is already open by looking for a matching .ldb file -- if an Access .mdb file is open, a .ldb file with the same name is created in the same folder to indicate that the database file is open/locked. We don't want to update a file that is already open by another user, to avoid data corruption.

The spreadsheet is saved to the user's desktop and closed for import. Then an instance of Access is started and the spreadsheet imported into the specified table. After a Msgbox is shown to confirm the import, the temp file is deleted from the desktop.

plain text version

VBA:

Option Explicit
' set a reference to Access Object Library before using this code
Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long
' function to get username
Public Function Username() As String
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
Username = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
End Function
Sub ImportReportToAccess()
'
' open Access database and append ss to appropriate table
'
Application.ScreenUpdating = False
' first check if db file is locked, if so then don't run!
If Dir("C:\databasefile.ldb") = "" Then
' If a .mdb file is open, a matching .ldb file with the same name is opened in the same directory
Dim AWName As String
Dim UserN As String
' the newest report
AWName = ActiveWorkbook.Name
ActiveSheet.UsedRange
UserN = Username
Workbooks(AWName).SaveAs "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName, FileFormat:=xlNormal
Workbooks(AWName).Close
Dim axsApp As Access.Application
Set axsApp = New Access.Application
With axsApp
.OpenCurrentDatabase "C:\databasefile.mdb", True
.DoCmd.OpenTable "table name"
.DoCmd.TransferSpreadsheet acImport, , "table name", "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName, True
.CloseCurrentDatabase
End With
MsgBox "import complete"
Kill "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName
Else
MsgBox ("Database file appears to be locked. Please try again later."), vbCritical
Exit Sub
End If
ExitProc:
Set axsApp = Nothing
Application.ScreenUpdating = True
End Sub

Enjoy,
JP

If you enjoyed this page:

Print This Post |

Email This Post | Permalink |

Subscribe now!

Filed Under: Access, Excel, VBA, automation
Tags: Access, database, Excel, import, VBA

This post has 1,079 views since February 4, 2008 – 1:53 pm.

4 Responses to “Import data to Access from Excel”
Great. What if the Excel file must be browsed? How do you use the dialog box in your code?

By JN on Oct 30, 2008
Hi JN,

Not sure what you mean. If you need to look at the file first, you can do that before you import to Access.

--JP

By JP on Oct 30, 2008
Hi,
I'm trying to update access with a certain data set from an excel file.. I just wanted to run the macro -not checking names/security.. so I used the below code:

plain text version
VBA:
1. Public Sub getdd()
3. Dim axsApp As Access.Application
4. Set axsApp = New Access.Application
5. Dim path As String
6. Dim pathXL As String
7. pathXL = ...path of workbook
8. path = ...path of database
9. With axsApp
10. .OpenCurrentDatabase path, True
11. .DoCmd.OpenTable "Upload Type"
12. .DoCmd.TransferSpreadsheet acImport, , "Upload Type", pathXL, True
13. .CloseCurrentDatabase
14. End With
15. End Sub
the table has two fields ... "Upload ID" and "Upload Type"... I mimicked the data on the excel spreadsheet.. and the recordset was just a few rows of dummy data: 1 Upload A, 2 Upload B, 3 Upload C....and so on. I keep getting an error:

Field "F3" doesnt exst in destination table "upload type". Nothing in that field on my Excel though... any ideas?

By AG788 on Dec 2, 2008
Make sure the field names are exactly the same in the Access table and the Excel spreadsheet. You might also want to check for trailing spaces or unprintable characters. I also recommend you not use spaces in field names. And you might want to not use the same name for the table and the field, that could confuse Access. Try that and let me know if it works.

By JP on Dec 3, 2008