Query to import a selected Excel file into Access

dbfromnewjersey
dbfromnewjersey used Ask the Experts™
on
I have an Access database with a table to be used for importing a periodic report from Excel. The table has 5 fields.


The format of the Excel import file will always be the same; that being the field names start on row 10 and there are 4 data fields. However,
the number of data rows will vary from one file to the next. Sometimes a file may have 100 records, another time it may have 150, etc, etc.

I want to skip over the first 10 rows (because I only want to import data contained in the 4 data fields) and terminate the import when
the row with "TOTAL" is reached. Or, in other words exclude the row with "TOTAL" in it was well as the first 10 rows.

In the Excel file, the month the report applies to always appears in cell C4. I would like to copy that value into Field5 of the Access table
for every single record.


Because this will be a recurring function, ideally what I'd like to be able to do from Access is click on a query name or a button on a form
and have it open windows explorer where I can navigate to and select the file I want.

Upon doing so, it would run an import automatically that includes and excludes the data as described above,

I have attached a sample Access database with expected results in its table as well as a sample Excel import file.



Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Generally, when I do this, I do one of two things.  In both cases, I use the FileDialog to open the Windows Explorer dialog box and select a file.  Lots of examples here in EE for doing this.

Then, because you have no way of knowing where the Totals line is located, I use Excel Automation to open the Excel file and this is where the two methods differ.

1.  In method 1, I determine where the Total line is located, then close the Excel File, and then use the TransferSpreadsheet method in Access to link the spreadsheet with specific cell ranges identified (eg A10:F100 ) in the Transferspreadsheet method.  This way, Access only links the rows you need as a table which you can then use in an append query to get the data into your destination table.

2.  In method 2, I simply delete the unwanted header rows from the spreadsheet, then locate the Totals row and clear the contents of that row and all subsequent rows, then save the workbook with a new name.  I then use this new name to link the remaining rows to my database and import them into my Access table.

After importing the data via method  1 or 2, I will generally delete the linked table, and with Method #2, I also delete the file that I modified and saved.
John TsioumprisSoftware & Systems Engineer

Commented:
IMHO TranferSpreadsheet is only useful when you are performing export/import without any modifications ..
If you want control over the process you need Excel Automation to "talk" to Excel directly and decide on each and cell what you want and what you don't
For start take a look here : Code to read Excel cells into Access table?
It might seem like a bit of hassle but the control over the process is rewarding and you can easily create a loop and import exactly what you want from where you want ...and put them again exactly where should be
Chief Technology Officer
Commented:
First of all.... what "attached file"?
That said, 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]"

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

I'll let someone else handle the Browse File function.

Good luck.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial