troubleshooting Question

Import an Excel File into Access

Avatar of dbfromnewjersey
dbfromnewjerseyFlag for United States of America asked on
Microsoft AccessMicrosoft Excel
11 Comments1 Solution75 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
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 11 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 11 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