Import an Excel File into Access

This sounds like a project spec.  Have you tried this yourself?   Do you have any control over the format of the spreadsheet?  Is it possible for the data to have either one or zero header rows so that the data begins on line 2 or 1?  This is the only layout that will allow you to do this without writing Excel automation code.  Another possible way of getting around the non-importable format is if there is a named range.  Access allows you to import named ranges.

I didn't download your .xlsm file and I won't because I don't want to run it on my PC.  I suggest that you upload an .xlsx file instead so we don't have to worry about having to allow macros to run.

If the spreadsheet can be reformated to look like a table, you can link to the spreadsheet using TransferSpreadsheet (one line of code) and then run an append query that will reformat the columns and append them to your permanent table.  If you can't reformat the spreadsheet, then it will require VBA which we can help you with.

This is easy using an SQL statement to import a block of data from an Excel workbook into Access.  I'll post below the response I recently posted in another thread authored by you that had the same/similar request.

"I have had great success using VBA to build Excel-into-Access queries on the fly.  It's my preferred method because it allows you to do just about anything you can do with a regular import query.  You can pick and chose any block of data from anywhere in the workbook, you pick & choose your columns to import, add additional info to be imported with the imported data, Add criteria, etc.  You don't have to open the workbook with code or have it open.  And... it is many times faster than line-by-line copy-and-paste - that's the hard way to do it.  Do it that way ONLY if forced to because of extenuating circumstances.

The code below shows how I do it.  It also shows how to include todays date as part of the query.

Dim strImportTableName as string
Dim strTargetFieldsList as string
Dim strSourceFieldsList as String
Dim blnHasHeaders as Boolean
Dim strExcelFilePath as String
Dim strSheetName as String
Dim strImportRange as String

‘SET VARIABLE VALUES:
strImportTablesName=”tblMyAccessTable”
strTargetFieldsList = “[AccessField1], [AccessFields2], [AccessField3]”
strSourceFieldsList = “[ExcelColumnName1], [Excel ColumnName5], [ExcelColumnName3]”
blnHasHeaders = True
strExcelFilePath = strFilePathFromBrowseFileFunction
strSheetName = “Sheet1”
strImportRange = “A10:E300”

‘BUILD THE IMPORT QUERY:
    strSQL = "INSERT INTO [" & strImportTableName & "]"
    strSQL = strSQL & "( [TodaysDate], " & strTargetFieldsList & ") "
    strSQL = strSQL & "SELECT Date() AS [TodaysDate], " & strSourceFieldsList 
    strSQL = strSQL & "FROM [Excel 12.0;HDR=" & IIf(blnHasHeaders, "Yes", "No") & ";Database=" & strExcelFilePath & "].[" & strSheetName & "$" & strImportRange & "]"

Select allOpen in new window

When you get done building your SQL query, it should look something like this:
strSQL = “INSERT INTO [tblMyAccessTable] ( [TodaysDate], [AccessField1], [AccessField2], [AccessField3] )”
strSQL = strSQL & “ SELECT Date() AS [TodaysDate], [ExcelColumnName1], [Excel ColumnName5], [ExcelColumnName3]”
strSQL = strSQL & “ FROM [Excel 12.0;HDR=Yes;Database=C:\Folder1\ExcelFileName.xlsx].[Sheet1$A10:E300]"

You can also include inline functions to separate the data coming in.  For example, you can use the function LEFT([ColumnCName],6) to put the left 6 characters into one column, then use MID([ColumnCName],7) to put characters 7 to the end in another Field.

Depending on the version of Excel you're using, you may have to use an earlier Excel version (9?).  Be sure to note the square brackets around the Excel file info and sheet/range, and the use of the $ between sheet name and range.  This is a sample.  You'll need to use your noodle and adjust it to your specifications.  I have set the number of rows beyond the actual number of rows, but you can run a cleanup query to delete any unwanted/needed rows, or add a WHERE clause that will filter out the unwanted stuff (i.e. empty key values column).

There are Excel functions that you can use if all you have are named ranges or Excel tables (you can also use TransferSpreadsheet to import a named range from anywhere in the workbook).  You'll need to get the Named Range or Excel Table's "RefersTo" sheet name and range to use here in an SQL query.

I've got an Excel and text file demo app that I use to teach and demo all the various ways you can import/export Excel and text files.  Let me know if you have a specific problem such as no header row, etc..

Good luck.

I don't know. I tried a stripped-down version of that code and it doesn't seem to be doing anything.   It doesn't fail when I step through the code but nothing happens, so I'm obviously doing something wrong.

A couple of other things:

Now trying it on a .xlsx file because of the concerns about macros being in a .xlsm file.
I'm using Excel version 15.0.
My sample .xlsx file is very basic and contains header names as you can see. But my production .xlsm files won't be containing any headers, so I don't know if I'm supposed to reference them by column letter or some other way. I've attached a sample database and a sample Excel file I'm trying to import.
Test-Database.accdb
TEST_FILE.xlsx

I'll return your database file with a working import.  I'll use both headers and no-headers so you can see the difference.

OK.  I didn't realize how much of a newbie you were.  I didn't include the code to run the SQL after the SQL was built:

CurrentDB.Execute strSQL

Select allOpen in new window

You also didn't have "Option Explicit" (to make sure you didn't make a mistake on your variable names) on your module, which wouldn't compile if you did.

The db I return will have a working import and a lot more.

OK Here it is.   Added "Option Explicit" to your form module (if you don't know why, google it).
I added some textboxes to your form.  Added a file picker.  Added an Excel file without headings.  Added some error handling.  Changed the Excel version to 12.  Moved the data block away from range A1 (so you could see it works from anywhere on the worksheet).
Select which file you want to use and click any button and see what happens.  It works for me.
TEST_FILE-No-Headings.xlsx
TEST_FILE.xlsx
Test-Database.accdb

Hi Mark,
It looks like the query you supplied requires that the data range be known.  I'm not sure that it is in this case.

I should mention that the data will always start on row 18  (columns C through E) and end with the row immediately above the word "TOTAL" in column C.

Thank you.

I had to come back and say that code is pure genius. Thanks for the help.

@dbfromnewjersey:  Thank you!  Glad I was able to help.