altering and then importing data from excel to access automatically

  • Thread starter Thread starter chrisbarnett.werneth
  • Start date Start date
C

chrisbarnett.werneth

Hi I'm a teacher and have created a reporting database using Access.
Uptil now i have been responsible for preparing the data but now need
to put it in the hands of someone with less computing experience and so
need to make the process as user-friendly as possible.

I am given the data as an Excel file. However the structure is wrong
and so i have created an VB module to kind of pivot the data on a new
worksheet. I then load up the Access database and have a macro which
imports the spreadsheet into a table.

I really need now to try and automate this much more. My aim is to
create a macro in Access that opens the Excel file, runs the VB pivot
routine and then import the data into the Access table automatically.

I also need to import some other information automatically into Access
from Excel files however because of the format recognition i have
always resaved the Excel file as csv first.

I hope this makes sense.

Thanks for any help you can give.
Chris Barnett
 
Hi Chris,

Cut out Excel completely and have the users directly enter the data into
Access either through a database form or a Data Access Page on the web.
 
Hi. thanks for the reply.

Unfortunately we use an internet based program to collect in the
attainment data and it exports only as excel files. I also have to
import other information from this system which also comes as an excel
file but for some reason needs putting through excel and resaving as a
csv file before Access will import it correctly. There may be other
ways but it seems the simplest.

I know that i could do the table pivoting in Access but i'm not sure
how to do it and have a routine working nicely in excel. I believe
that i can control Excel from within Access so i think that is the
easiest way forward.

One of my excel files is called "General Pupil Data" and it needs
loading into excel and then resaving as a csv file before importing
into Access. I would be grateful to know how to do this as part of
using either a command button on the page or a macro.

Thanks
Chris Barnett
 
Back
Top