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:
- Open MS Access
- Click Alt+F11, insert a new module, then paste the below code into the code window
- Add a reference to excel objects libref (see screenshot below)
- copy the path of your Excel file to the line starting str1 = below
- 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
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.