Importing Excel spreadsheets into well defined MS Access Applicati

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear friends,

If anyone can help me with this, it would be so so appreciated.

I prepared an excel format(20 protected worksheets in a workbook) which is
supposed to be filled by 15 projects (located remotely) in every quarter and
then send it to me.
Now, I end up having a huge data.

Would it ease if I migrate to Access? If Yes, how to put the formats along
with the data in the form of tables

Please help me to consolidate the data so that reports can be drawn easily?

Your help on this would be very much appreciated, as it has had my mind in a
twist for the past 2 weeks. And I'm quite new to this data migration
phenomenon.

Thanks in advance
 
I think that you will need to unprotect the excel files, but assuming that
each worksheet contains only text with fieldnames in row 1 and data in rows
2-... this will not be a problem

As long as you only need to import this data once, just use the File/Get
External Data/Import and choose each of your excel workbooks and then the
worksheet.

If you are going to have to load this many times you will want to write VBA
code and you will use the

Docmd.Transferspreadsheet

method for this.

Please let me know if you need more assistance.
 
Thanks for the reply.
I have explored many ways and finally concluded that while protecting the
worksheets I'll allow the feature: "select loced cells & unlocked cells"
(which can be chosen at the time of protecting the worksheet) and will then
send it to all the 15 projects.

It will be helpful for me to at least select (copy/paste) the portion from
the data files as sent by the projects.
I can create a master data base after doing copy, paste from all the
worksheets as sent by the projects.
It will avoid the tedious steps to unprotect each and every worksheets
seperately.

Once I have all the data in mastersheet I can easily import it in Access.

Please give your advice if am going on a right track.
 
It kind of sounds manual to me. You can write code that will open all of your
workbooks, save them without a password, and then go through each worksheet
in the workbooks to unprotect them. You can even run that from access if you
create a reference to excel. You can use this code

Dim ws As Worksheet
Workbooks.Open "fullpathname", , , "password"
Workbooks("name").SaveAs "fullpathname", , ""
For Each ws In Workbooks("name").Worksheets
ws.Unprotect "password"
Next
Set ws = Nothing
 
It sounds good. I really liked the idea of writing codes. But, at the same
time am new to it. Thanks for giving the written code.

Please guide more about codes if possible. Where can I start writing the
codes (both in case of EXECL and ACCESS), if (as you wrote) I want to open
all of my workbooks, save them without a password, and then go through each
worksheet in the workbooks to unprotect them.
 
Back
Top