xl to access db

Convert an Excel Workbook to Access Database

Being busy for the past month with a new baby at home I have not posted for a while. I have also been very busy at work. Here is a technique I used a few weeks ago for converting an Excel workbook into a database.

I mistakenly thought there was built-in functionality in Excel to do this. But alas—there was not! So here is my code for getting around it. To use this all you need to do is:

    1. Open MS Access
    2. Click Alt+F11, insert a new module, then paste the below code into the code window
    3. Add a reference to excel objects libref (see screenshot below)
    4. copy the path of your Excel file to the line starting str1 = below
    5. Click F8 to step through the routine
Convert Excel Workbook to Access Database
Sub getxl() 'routine for converting an excel workbook to a database

Dim str1, str2 As String
Dim xl As New Excel.Application 'must add ms excel objects libref for this
Dim bk As Workbook
Dim sht As Worksheet
Dim i, j, k As Integer

str1 = "C:\Users\cgoldie\Downloads\testme.xlsx" 'add the path of your workbook here!
Set xl = Excel.Application
xl.Visible = True
Set bk = xl.Workbooks.Open(str1) 'open the workbook
i = bk.Sheets.Count 'count number of sheets
ReDim arr1(1 To i) As String 'write sheetnames to an array
For j = 1 To i
Debug.Print bk.Sheets(j).Name
arr1(j) = bk.Sheets(j).Name
Next j
bk.Close False 'close the workbook

For j = 1 To i 'import each sheet as a new access table without headers. the table name = the sheetname
str2 = arr1(j)
Debug.Print str2 'writes the current sheetname to immediate window for your convenience
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, str2, str1, False, str2 & "$" 'transfers the current sheet to access
Next j

End Sub
Adding libref to Excel Object Library
convert xl to access db
convert xl to access db


Note that this routine will ignore headers and instead it will name fields F1, F2, etc. corresponding to columns from your workbook. To get my Access file click here.


Share your thoughts below :)