We help IT Professionals succeed at work.
Get Started

Import an Excel File into Access

70 Views
Last Modified: 2019-11-29
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
Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE