Avatar of Jenkins
Jenkins
Flag for United States of America asked on

Import an Excel File into Access

I've attached an Access database with a Form containing a command button that is to be used to select an Access file for import.

The button contains FileDialog code that works and that I take no credit for having written.

I've attached an example of the type of Excel file I need to import.

You will see it contains 3 columns of data that begins on row 18 of columns C, D and E and ends on row 22 of columns C, D, and E.

At a minimum, via use of the command button already on the form where I select a file for import/transfer, I'm trying to bring

those 3 columns and 5 rows of data into an Access table.  I don't care if it goes into a new or existing table.

Ideally though, what I'd like to be able to do is have the first 6 digits of column C (which represents the account number) go into its own column, the remainder of column C go into its own column, the Statistics go into its own column, the Dollar Amount go into its own column, and the date information (in cell D6) go into its own column and appear in every row of data so that the 5-column expected results table will look something like this:


Field1       Field2                                               Field3       Field4           Field5
111111   Description of Account 111111       10           10.00     25-NOV-2019  
222222   Description of Account 222222        5          100.00     25-NOV-2019
333333   Description of Account 333333      100     3000.00     25-NOV-2019  
444444   Description of Account 444444       25         250.00     25-NOV-2019
555555   Description of Account 555555       40           80.00     25-NOV-2019


Also, because this will be a recurring task and the number of data records will vary from one file to the next, 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.
Test-Database.accdb
Test-File.xlsm
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
PatHartman

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.
Mark Edwards

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 & "]"

Open 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.
Jenkins

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mark Edwards

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

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

Open 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.
Mark Edwards

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jenkins

ASKER
Thank you.
Jenkins

ASKER
I had to come back and say that code is pure genius. Thanks for the help.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Edwards

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