Importing a large number of spreadsheets from Excel to Access (1 Viewer)

etahne10

New member
Local time
Today, 20:23
Joined
May 23, 2022
Messages
3
Hello! I am currently working on a project where I have to import data from numerous spreadsheets into an access database. The Excel workbooks are separated by months and each individual spreadsheet represents a day in the month. Within the spreadsheet itself, there is no information about the date. Moreover, the data in the excel spreadsheet is presented as a form, hence there are numerous cells with redundant information that I do not need in my database tables. Is there any way I can create a form that allows me to specify the Excel worksheet/workbook I would like to import from, the cells I would like to import, and to add an additional field that lists the date the data was imported from for every record that I import into my table? Thank you for reading my post and I appreciate any help I can get. Please feel free to ask if you have any other further questions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:23
Joined
Oct 29, 2018
Messages
21,357
Hi. The short answer to your question is Yes, that's possible. However, I think you're talking about writing a bunch of code that a new user may not know yet how to do. How many files do you have to import? You might just have to it manually.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2013
Messages
16,553
Agree with DBG

Suggest provide an example of your spreadsheet highlighting the data do you need to import, together with details of the table you need to populate.

It is easy enough in principle to just ignore the data you don't require using an insert query with appropriate criteria.
 

etahne10

New member
Local time
Today, 20:23
Joined
May 23, 2022
Messages
3
Hello! Thank you for your responses. I have over 5 years' worth of spreadsheets that I need to import so I would prefer if I didn't have to do it manually :( I am unable to show you the exact spreadsheets due to confidentiality constraints, but within each spreadsheet itself, the cells between B7:E1XX contain the information I require. I say E1XX as the range is not fixed since we different number of clients every day. When I import a spreadsheet, the columns B, C, D, and E in the spreadsheet can be assigned to the fields Incoming Client, In Amount, Outgoing Client, and Out Amount in my Access table respectively. I require a 5th field that states the date on which these transactions occur. Currently, the date of the transaction is stored as the name of the spreadsheet itself eg. the workbook is called May and within it there are 31 spreadsheets. I really appreciate all your help and if coding is required could you point me in the direction to find resources to help me learn and understand what to do? Thank you all so much.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Feb 19, 2013
Messages
16,553
At least provide the column headings. The fact they start on row 7 shouldn't matter. Ditto the number of worksheets. The varying number of columns can be a problem but without knowing what you are doing with them, difficult to say.

Really don't want to get into a guessing game as to the actual requirement since we still have no clue has to what your destination looks like so I'll just point you in a direction for you to investigate.

Research transferspreadsheet - https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

and for your varying columns you will probably need a union query

Finally you will need vba to loop through your work books and then loop through the worksheets

with regards the date, you should be able to work that out from the the folder for year, the workbook for month and the worksheet for day.

your code will be along the following lines

Code:
loop through folders for year
     loop through worksheets for month
        loop through worksheets for day
            transferspreadsheet
            execute insert/update query/s
       next worksheet
   next workbook
next folder
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:23
Joined
Jul 9, 2003
Messages
16,244
This old answer to a similar question may be of interest:-


Might offer some inspiration...
 

Isaac

Lifelong Learner
Local time
Today, 05:23
Joined
Mar 14, 2017
Messages
8,738
+1 what CJ said, so first learn how to use transferspreadsheet really well in a single case.

after that, learn to make a Sub with a parameter. the only input parameter you might need is a full file path of the excel, just depends on your case.

after that, use File Scripting Object (FSO) to loop through a folder and execute the sub on each file.

Code:
dim fso as object, fsofile as object, strFilePath as string, strFileName as string, fsoFolder as object

set fso=createobject("scripting.filesystemobject")

set fsofolder = fso.getfolder("path to folder")

for each fsofile in fsofolder.files

     if right(fsofile.name,3)="xls" then

          SubName fsofile.path

    end if

next fsofile
 

Users who are viewing this thread

Top Bottom