Import Process For Multiple Excel Workbooks to Single Access Table (1 Viewer)

SazzleTWG

Registered User.
Local time
Today, 22:24
Joined
Apr 18, 2012
Messages
10
Hi all,

I am working with several identical time and motion spreadsheets that are updated daily and saved in one folder on a shared drive. What I need to do is write some VBA code to run a daily automated process to import the new data from the spreadsheets into a single access table to enable reporting. The only problem is that I am a VBA newbie and don't really know where to start! :confused:

If anyone could help me with this I would be very grateful?

Many thanks
 

Alansidman

AWF VIP
Local time
Today, 17:24
Joined
Jul 31, 2008
Messages
1,493
If the spreadsheets are updated daily, is this done in an existing spreadsheet or are new ones created each day. If it is an existing spreadsheet that is update, why not link it instead of importing and it will be automatically updated each time you open Access.

Alan
 

SazzleTWG

Registered User.
Local time
Today, 22:24
Joined
Apr 18, 2012
Messages
10
Hi Alan,

Thanks for your reply, it is one spreadsheet which has extra lines added each day rather than a new one being created. I have thought about just linking them but then I would need to add a new link every time a new spreadsheet is added (as there is currently a spreadsheet per associate) and it would create a table per spreadsheet instead of just the one that I would prefer.
 

Alansidman

AWF VIP
Local time
Today, 17:24
Joined
Jul 31, 2008
Messages
1,493
Ok. Suggest you delete the existing table(s) each day and then import the new spreadsheets. I had to do this previously. Here is a small db that does just that. It was built using Access 2003 and Excel 2003 in an XP environment. You will need to change some of the VBA to conform with later versions if that is something you need. You may also have to adapt the path to your systems. But this should be a good start for you to see how to make it happen.
Alan
 

Attachments

  • GEMCAP.zip
    110.1 KB · Views: 140

Users who are viewing this thread

Top Bottom