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