Export Access Data to Excel/Reimport modified data into Access

[mlloulou]

Hi - I hope someone can help me.

I have been given a task to modify an existing database. We have a number
of companies that we fund with money for specific projects. Our db currently
tracks the key measurables and performance results from these companies (all
are quite different and have different key measurables), the report date for
the data, and sets targets and performance ranges. They report to us each
quarter on their specific key measureable results. Currently, these are
coming into us in all different formats which we manually update each
quarter. What we would like to do now is export to excel (for the purposes
of gathering the results in a consistent manner), the required items each
company is to report on, together with their specific targets, result report
dates, etc. The companies will take the excel workbooks we send to each of
them, update their required results and send them back. We would then like
to upload the actual results into our db. We have a field in our db called
Actual Results where this data would go.

I was thinking I would like to set up an excel template that I would map the
exported fields to, save the file as a new name and email the workbook to
each company. When I get the data back, I was thinking it might be better to
upload all at once rather than on a individual basis. But I'm not sure what
the best way to go is. I am open to better suggestions - this is just me
thinking out loud.

Mostly, I would like to know 2 things -

1. Is this even possible?
2. How can it be done?

 

[Mark A. Sam]

This is an old post and likely you found a solution, but I'll render a
solution anyway.

I think the easiest thing to do to export to excel is to use the export to
Excel function. I don't know what version you are using or which menu item
it is. There is also a button for this functionality. If it isn't on a
tool bar you could place it there. Then set up the data in a query and
export it. You can also import the data from Excel by using an addin
available at this site: http://www.sqlexcel.net. I tried this an it works.

To go the other way, from Excel to Access, simply link the tables. This
will require that the reconfigured worksheets be in fixed format. Then when
a new worksheet replaces an older one, Access will be able to find the data.

God Bless,

Mark A. Sam

 

[mlloulou]

Mark -

Thank you so much for responding. I have been muddling around for a month
and had given up hope... so thank you for taking the time to help me. I had
sort of given up hope, so it was very nice to get a response.

This is what I have accomplished to date -

I modified the database and streamlined the key measureables. I have been
able to successfully export the data to excel based on 3 queries that each
create a worksheet in the excel template using the transferspreadsheet
method. I have matched each section of the excel template using vlookups and
match fuctions to populate the template from the exported worksheets when a
unique tracking id is input into the top of the form. Just today I have
created a macro to copy the template worksheet into a new file and name it
the company name. Now we can email the unique files to each company in excel
format. YEAH!!

So, now when we get the forms back from each company, do you suggest that
the actual results data that I want to re-import be consolidated in a single
worksheet? Then link the excel file to the database? Or would you do it on
an individual worksheet basis where you append each companies data to an
existing table. When you speak of the worksheets being in fixed format, do
you mean each company we request data from should appear on the same cell
reference. For example, on my template, I have allowed 10 rows for one type
of measureable, and 10 rows for another type. Not all companies have the
same number of key measureables, but each form has the same 10 rows even if
some are blank. Does this meet your criteria for the worksheet being in
fixed format? Also, one more question, when I get the data into the worksheet
and link it to the database, how do I ensure that the old data is not lost or
written over if I use the same template to link to each time?

Thank you so much for your time.

 

[Mark A. Sam]

ML,

I don't have time to look at this at the moment, I'll be tied up until this
evening, but will address it later tonight or in the morning.

God Bless,

Mark

 

[mlloulou]

Mark - Thank you so much for replying. I have been muddling through this
for the past month. I had given up hope, but I am certainly glad you took
the time to offer some help.

Here is what I have done so far (bear in mind I only get to work on this a
bit at a time as I have other duties at work that take precedence). I built
a template in excel and 3 make table queries in access. I can now export the
data to build the template for each company through the TransferSpreadsheet
method from Access which creates three new worksheets in the excel template.
I mapped the fields in the template using vlookup and match functions to
populate each cell when a unique company code is input at the top of the
form. I then wrote a macro to copy the single template worksheet to a new
file and it autosaves as the company name stored in one of the cells. Then we
can send this new file to the company it belongs to via email and they can
use it to fill in their actual results. So far so good.

Now for the import feature which I haven't started yet. If you don't mind,
I have a few questions about that part if I may.

1. Do you suggest that I use the master template to create another
worksheet that will be the import data that is in a consolidated list (from
all the individual spreadsheets of the companies we are tracking) that I will
link to the access db?
2. As far as a fixed worksheet - does this mean that each company must have
the exact same template and they input data into the same cells? I just want
to be clear about this because each company has their own key measureables
and not every one has the same number but there are 10 designated fields for
the data - some will just be empty and others will have data to import.
3. I'm not sure I understand about the reconfigured worksheet - also I do
not understand how the data will be updated. For example, we send out this
template 4 times per year - Sep 30, Dec 31, Mar 31 and Jun 30. The same
unique key measureables will be measured each time for each company, plus
there may be more at year end. The database is set up to track a measureable
by date. However, how will I append to my table and make sure the prior
quarter's data is not overwritten? I guess I'm just not sure how to
organize the data that I will import and then how will I ensure that I don't
overwrite the prior data each quarter.

Thanks for your time. I really appreciate any help you have to offer.

Regards,
ml

 

[Mark A. Sam]

Now for the import feature which I haven't started yet. If you don't
mind,
I have a few questions about that part if I may.

1. Do you suggest that I use the master template to create another
worksheet that will be the import data that is in a consolidated list
(from
all the individual spreadsheets of the companies we are tracking) that I
will
link to the access db?

No I am saying to link the individual worksheets rather than importing them.
When you link the sheets the data is there for your use. You don't need to
do anything else, unless you want to manipulate the data again inside of
Access.

2. As far as a fixed worksheet - does this mean that each company must
have
the exact same template and they input data into the same cells? I just
want
to be clear about this because each company has their own key measureables
and not every one has the same number but there are 10 designated fields
for
the data - some will just be empty and others will have data to import.

It means that that when they have completed doing what they are doing, that
the end result is always the same format. The column names are the same and
start in the same position on the worksheet.

3. I'm not sure I understand about the reconfigured worksheet -

You said that the users were going to change the exported data. That is
what I meant.

also I do

not understand how the data will be updated.

You said the users were going to update the data.

"The companies will take the excel workbooks we send to each of
them, update their required results and send them back. "

When they send them back, each time the resulting worksheet should be layed
out the same way for the linked tables to maintain their integrity.


For example, we send out this

template 4 times per year - Sep 30, Dec 31, Mar 31 and Jun 30. The same
unique key measureables will be measured each time for each company, plus
there may be more at year end. The database is set up to track a
measureable
by date. However, how will I append to my table and make sure the prior
quarter's data is not overwritten?

I don't know enough about your process to answer that. It is out of the
scope of your original post.

I guess I'm just not sure how to

organize the data that I will import and then how will I ensure that I
don't
overwrite the prior data each quarter.

It seems like this is more complicated than your experience level allows.
It might be a good idea to suggest that your company hire an exerienced
Access developer to so some of the tasks that are too difficult for you.
What you are putting forth is out of the scope of this forum.

Thanks for your time. I really appreciate any help you have to offer.

No problem, but I hope you understand that I can't do your development work
for you. This is only meant to help with specific problems or to help guide
others with approaches to problems.

God Bless,

Mark