import from excel with data validation

[Jey]

I want to design a tool to import data from an excel workbook with multiple
tabs into multiple tables in a database. The excel sheet will be used for
field staff to enter data into, and will be set up ahead of time so it will
be consistant as far as field names etc. They specifically want excel (I
tried to sell them on the idea of distributing a data entry package in
access, but they didn't go for it!).

Most importantly I want the tool to validate the data first and give the
user a chance to fix any entries that don't match the data type of the field
it is supposed to be going to! I want the basic flow to be:
-allow the user to navigate to the file they want to import
-check the data
-if the data has problems, generate a report for the user
-if the data is OK, proceed with importing it
(but I'm open to alternate suggestions!)

I basically want it to do what the 'import from excel' wizard does, but
multiple times per workbook with the tab & table selections automated, and
with a detailed error report generated in case of a failure to import! Plus
some 'pre-import' formatting on the excel sheet (ex. converting all formulas
to values). The other complication is that some data from one tab will have
to be copied to multiple records on subsequent tabs. For example tab1 has
weather data for the day, creating 1 record in table1. The ID of that record
created in the database has to go into a field for each record on tab2/table2.

I'm fairly new at VBA, so I'm having trouble even getting started. I'd
appreciate it if someone could give me some ideas on what might work for the
various steps?

Thanks,
Jey

 

[pietlinden]

I want to design a tool to import data from an excel workbook with multiple
tabs into multiple tables in a database. The excel sheet will be used for
field staff to enter data into, and will be set up ahead of time so it will
be consistant as far as field names etc. They specifically want excel (I
tried to sell them on the idea of distributing a data entry package in
access, but they didn't go for it!).

Most importantly I want the tool to validate the data first and give the
user a chance to fix any entries that don't match the data type of the field
it is supposed to be going to! I want the basic flow to be:
-allow the user to navigate to the file they want to import
-check the data
-if the data has problems, generate a report for the user
-if the data is OK, proceed with importing it
(but I'm open to alternate suggestions!)

I basically want it to do what the 'import from excel' wizard does, but
multiple times per workbook with the tab & table selections automated, and
with a detailed error report generated in case of a failure to import! Plus
some 'pre-import' formatting on the excel sheet (ex. converting all formulas
to values). The other complication is that some data from one tab will have
to be copied to multiple records on subsequent tabs. For example tab1 has
weather data for the day, creating 1 record in table1. The ID of that record
created in the database has to go into a field for each record on tab2/table2.

I'm fairly new at VBA, so I'm having trouble even getting started. I'd
appreciate it if someone could give me some ideas on what might work for the
various steps?

Thanks,
Jey

I hate to say this, but Access is much better at validation as the
data is entered than Excel is. This is going to be a serious hassle.
This would be pretty straightforward in Access. Create your tables,
enforce Referential Integrity, create validation rules for the fields
you need to, and you're pretty much done. The code, if you need any,
would be relatively minor. I get the feeling that this will be
anything but that - IMO, Excel is the wrong tool for the job.

 

[Jey]

Ha ha ha... that's what I told them! Maybe my time would be better spent
convincing them to use Access rather that trying to mess around with excel
for this! As a wise professor once told me "never NEVER put data into excel
unles your doing calculations on it". Unfortunately, it's what everyone uses
and is comfortable with. So in the mean time I'll continue with this
excercise. If it doesn't kill me it will make me a better VBA programmer!

Jey

 

[pietlinden]

Ha ha ha... that's what I told them! Maybe my time would be better spent
convincing them to use Access rather that trying to mess around with excel
for this! As a wise professor once told me "never NEVER put data into excel
unles your doing calculations on it". Unfortunately, it's what everyone uses
and is comfortable with. So in the mean time I'll continue with this
excercise. If it doesn't kill me it will make me a better VBA programmer!

Jey

I would do a quick demo application to show them what you mean. I did
that at work and FINALLY after like 4 months, they let me write a
properly normalized database with a front end (imagine taking 4 months
to decide that was okay!) After that my life was MUCH easier.

 

[paulgu]

Validation in Excel can be harder than in Access, depending on the type of vaildation. However Excel does have some useful facilities for controlling and validating inputs. If I were you I would do my best to avoid a 2 stage validation process - getting it right first time is much easier in the long run.
I notice that you talk about field staff and guess that they need to enter data at locations where they can't access your network and that may well be the reason for using Excel (though a local Access database could also be used). If the data they need to validate against is dynamic, eg using a list of names which gets changed from day to day, then you have a problem (especially if the turnaround time means that the data may have changed again in the meantime). However if it's static then you should be able to do all the validation at the time of entry and avoid the reporting back of errors. In either case I would suggest you focus on getting as much of the validation as possible done at the point of data entry.

 

[Klatuu]

I am with pietlinden on this. This chance that you will ever get a clean
import is minimal. Excel is really the wrong tool for this sort of thing.

Some things you can take to the decision makers.
1. It will be less expensive for them to use Access for data entry than
Excel.
There are extra operations required and there is the iterative process of
getting the data right. Using Access, the user will not be allowed to enter
incorrect or incomplete data. The productive time loss using Excel will be
significant. Even if the entry is correct, importing sometime gets field
types wrong and the data has to be hand manipulated to get it to import
correctly. Using Excel's flat file contruction, there is no way to ensure
any referential integrity. All these issues add up to lost productivity and
thus money.

2. Data in Excel spreadsheets is totally unsecure. Sure, you can put a
password on an Excel workbook, but then you have to remove it to be able to
import the data into Access.

3. If the decision makers are concerned about the cost of deploying Access
to all users, that is not necessary. A one time purchace of the Developer's
Tools is all that is required unless you are using 2007, then the are free.
This allows you to deploy a runtime version of Access to the users so they
can use the application. This is actually a good thing. You can deliver an
mde or accde file to the users and they will not be able to make any
modifications to the applications or make any changes to the data without
going through your user interface. It is an added security layer.

So take your lance and your talking points and go tilt with the windmills.

 

[Jey]

I agree, and I'll bring it up again with them!

I am with pietlinden on this. This chance that you will ever get a clean
import is minimal. Excel is really the wrong tool for this sort of thing.

Some things you can take to the decision makers.
1. It will be less expensive for them to use Access for data entry than
Excel.
There are extra operations required and there is the iterative process of
getting the data right. Using Access, the user will not be allowed to enter
incorrect or incomplete data. The productive time loss using Excel will be
significant. Even if the entry is correct, importing sometime gets field
types wrong and the data has to be hand manipulated to get it to import
correctly. Using Excel's flat file contruction, there is no way to ensure
any referential integrity. All these issues add up to lost productivity and
thus money.

2. Data in Excel spreadsheets is totally unsecure. Sure, you can put a
password on an Excel workbook, but then you have to remove it to be able to
import the data into Access.

3. If the decision makers are concerned about the cost of deploying Access
to all users, that is not necessary. A one time purchace of the Developer's
Tools is all that is required unless you are using 2007, then the are free.
This allows you to deploy a runtime version of Access to the users so they
can use the application. This is actually a good thing. You can deliver an
mde or accde file to the users and they will not be able to make any
modifications to the applications or make any changes to the data without
going through your user interface. It is an added security layer.

So take your lance and your talking points and go tilt with the windmills.