troubleshooting Question

Query to import a selected Excel file into Access

Avatar of dbfromnewjersey
dbfromnewjerseyFlag for United States of America asked on
Microsoft Access
3 Comments1 Solution58 ViewsLast Modified:
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.
Mark Edwards
Chief Technology Officer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros