Easiest Way to have access functionality in Excel or vice-versa?

[John]

Hello.

I'm after some advice from any people in here proficient with the
Excel/Access/Word programs.

This is a bit of a difficult one as I am not sure which direction I
need to take to do what I want.

I have a form design in Excel for individual records containing
different fields. It is all set out the way I want.

I have a spreadsheet with lots of different records that I need to add
manually within the layout in Excel, and then print out.

I would like to be able to just click a button and it fetches all the
info into the layout in Excel and prints it. Or to have a button where
I can go from one record to the next and print them all individually.

What is the best way to go about this?

The desgin/layout I have in Excel is perfect and exactly how I want
it. It is just a bit of a pain to type all the info into all the
fields manually from the separate spreadsheet with all the records
listed. I get different records every week 80 to 120 of them on a
spreadsheet with several columns of data that I am currently copying
manually into this design one row at a time and printing out.

I am not sure whether I would be able to have a form design in Access
exactly the way I have the layout in Excel?

The only other two words coming into my head are "mail & merge". I am
thinking, maybe use Word? Perhaps I could copy the layout in Excel
into a Word document. Then maybe there is a way to import all the data
from the other excel spreadsheets I get into Word and perform a mail
merge to print it all in one go? Or can you only add mail merge data
individually?

Perhaps Access would be the best way to go in the long run? It is one
of the MS programs I am least familiar with though. I have mainly used
Word and Excel in the past, but I don't have very much experience of a
mail-merge to say the least.

I'd really appreciate your ideas/suggestions on this. I'm not a
complete expert or anything when it comes to office packages though I
am familiar with them all.

Thanks

John

 

[Douglas J Steele]

It sounds to me more like you need an Access database than an Excel
spreadsheet. In my opinion, Excel is far better for analysing data, rather
than working with the data one row at a time. Recognize, though, that you
can't just take a spreadsheet and implement it in Access. To be effective,
an Access application should have a properly normalized database.

I'd be very surprised if you could create a form in Excel that couldn't be
reproduced in Access.

I don't see anything in your writeup that suggests that you're actually
going to be mailing the reports, so I'm not sure how mail & merge fits into
the equation.

 

[Doug Robbins]

Even as a Word MVP, I would second what my fellow MVP Doug has suggested
that Access is the best choice. Note however, that while you will have to
design the form from scratch in Access, all of the data can simply be copied
and pasted, or imported into Access.

That said, it would also be possible to create the form as a mailmerge main
document in Word and use your Excel data as the source for a mailmerge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Tom Ogilvy]

If I correctly understand the requirement, (transfer data one at a time to
fill in fields in a form, then print the form, ding this across a
"database"/rows of records):

Then, actually, it seems like a perfect match for mailmerge with Excel as
the data source, if the user can layout the word document to match his/her
requirements.

or

the transfer of the data to a "form" and then printing, doing this in a
loop over each row could be done fairly trivially in Excel with a macro.

Access would probably be good as well if the OP wants to start from scratch.

 

[John]

It sounds to me more like you need an Access database than an Excel
spreadsheet. In my opinion, Excel is far better for analysing data, rather
than working with the data one row at a time. Recognize, though, that you
can't just take a spreadsheet and implement it in Access. To be effective,
an Access application should have a properly normalized database.

I'd be very surprised if you could create a form in Excel that couldn't be
reproduced in Access.

I don't see anything in your writeup that suggests that you're actually
going to be mailing the reports, so I'm not sure how mail & merge fits into
the equation.

Thanks for the reply. I have gone the Access route and have made some
great progress so far.

I imported the spreadsheet data from Excel to Access.

I went through the wizard to create a form.

I then created another form in design view and copied the original
form/layout design from Excel to Access. I then copied the fields
from the first form the wizard made into the right sections of the
design.

I am just a little stuck now though.

The form that the wizard made is okay in Form View and displays all
the data from the table, but the form I made and copied the fields to
has strange words in all the fields and isn't getting the data from
the table.

It is displaying #Name? in all the fields. And there are no records to
move to the next one etc in Form View as with the form the wizard
created.

Do you know how I can fix this so it relates to the right fields from
the table?

I am guessing I need to click on each field and then go to properties
and there must be something in there I need to do to get it to relate
to the table data?

Thanks very much for any help

John

 

[Douglas J. Steele]

The form that the wizard made is okay in Form View and displays all
the data from the table, but the form I made and copied the fields to
has strange words in all the fields and isn't getting the data from
the table.

It is displaying #Name? in all the fields. And there are no records to
move to the next one etc in Form View as with the form the wizard
created.

Do you know how I can fix this so it relates to the right fields from
the table?

I am guessing I need to click on each field and then go to properties
and there must be something in there I need to do to get it to relate
to the table data?

Have you got the correct record source for the form? If so, then yes, you'll
probably have to go into the properties for each control and change the
control source property.

 

[Al Camp]

Doug,
If I could slip this note in here...
A few days ago (in an old post) we discussed using the "filter" for a
form as the filter for a report...
Dirk Goldgar, you, and I, thought it would fly...
Well, it does... and it really works great!

In the past I've done all sorts of convoluted parameter queries, or built
complicated criteria forms, or used complicated multiple "argument" strings
in the OpenReport function... all trying to obtain report "flexibility".
What a pain!

Here's the code I use... couldn't be simpler...

Private Sub Report_Open(Cancel As Integer)
If Forms!frmCustomers.FilterOn = False Then
Me.Filter = "CustID = Forms!frmCustomers!CustID"
Me.FilterOn = True
Else
Me.Filter = Forms!frmCustomers.Filter
Me.FilterOn = True
End If
End Sub

When the user is looking at all the records, the report only prints the
one record being displayed.
If the user filters the form recordset (ex. by City returning 100
records), the report prints those in a "batch"

Thanks for the help,
Al Camp

 

[aaron.kempf]

dont get discouraged; keep up the good work!!

excel is a disease--- i am glad that you're going in the right
direction.

still dont know what you mean by
In my opinion, Excel is far better for analysing data, rather
than working with the data one row at a time.

what do you mean analyzing data?

i mean seriously here-- i just dont believe that you should be allowed
to say those types of things.. if ANYTHING is strong at analyzing data
(looking at it across multiple levels and or hierarchies) then i say
Access is MUCH better than Excel for 'analyzing data'.

i think that excel is crap; and 50 of corporate america-- is making the
wrong decision by letting these idiots use Excel.

train them on Access or Crystal Reports-- I'm just burnt out on
recreating the same XLS week in and week out

-aaron

 

[Harlan Grove]

(e-mail address removed) wrote...
....

excel is a disease--- i am glad that you're going in the right
direction.

....

Nice Troll!

Didn't take long for this to become an 'advocacy' thread.