Using Excel as a data base
 

I am using excel as a data base. It is very large What I am trying to do is
from word I want to look for specific data lets say like AD-02.56 then find
all the matches in the database and return the row that contain the serch
data i am lookingf for. it could be up to 20 rows that may have that data.
Is there a book that I can look for that can help me out in writing the macro
or what ever i will need to pull the information from excel into the word
document? I have 8 colums of information and like 500 recorde (rows) and so
on. I am just looking for a good book to get started on how to use excel as
a database.

Thank you for you help.

J.W.

Using Excel as a data base
 

I probably need to say first off that if you have database requirements (IE
you need to 'query' a table or tables of data) then XL isn't really the tool
for the job and can prove quite frustrating when you try to do database
things with it.

Obviously your spreadsheet has been growing for some time and it is very
large as you say so scrapping it is not an option. One way to make your life
easier would be to link your XL worksheets to an Access database. From there
you can use the power of queries to find the data you are looking for in an
efficient fashion.

You will still need to understand how to automate the look-up task from
within Word but it will be easier as you will have less to do because the
queries will do the lookup work you would have to code yourself in XL. You
should also be able to write a good portion of your code in Word
automatically by 'recording' a macro to kick off. Record the steps outlined
in the help files - Search for "Use Microsoft Query to retrieve data from an
external data source" in Word Help as a starting point.

As for books, I find MS branded publications to be safe bets. Look for an
advanced book on Word that has plenty to say about the VBA side of things.
Any good technical bookshop should have something along these lines. As for
finding a book about how to use XL as a database, I'd be surprised if you
could find one at all because ultimately you can't (or at least should be
discouraged from trying). You'd need to create your own database 'engine' in
code behind your workbook, or the fundamentals of one at the very least.
Keep in mind, Xl is a spreadsheeting application, not a database.


Good luck

Mr. Smith


"J.W.R." wrote:

I am using excel as a data base. It is very large What I am trying to do is
from word I want to look for specific data lets say like AD-02.56 then find
all the matches in the database and return the row that contain the serch
data i am lookingf for. it could be up to 20 rows that may have that data.
Is there a book that I can look for that can help me out in writing the macro
or what ever i will need to pull the information from excel into the word
document? I have 8 colums of information and like 500 recorde (rows) and so
on. I am just looking for a good book to get started on how to use excel as
a database.

Thank you for you help.

J.W.

Using Excel as a data base
 

Hi JW,

Is the transfer to Word really needed in this manner?

As Mr Smith says Excel is best as a spreadsheet application not a
database. In saying that, your outline of 500 rows is not large at all.
I would recommend setting your data up so that every column has a
header, selecting the whole range & creating a pivot table (ie [alt + d
+ p]) based on the data (& if required, copying the pivot data into
Word).

Once set up pivot tables are quick ways of manipulating data & the
summarised data could be easily copied in a number of ways eg inserted,
plain text or as a table.


Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=556719

Using Excel as a data base
 

Thank you both for your suggestions and I will look into this a little
further. The reason for not using access is that it is not available where I
work at.
And I do have headers for each column of data. Again thank you both.

J.W.
--
5 P''s of Life
Prior
Planning
Prevents
Poor
Performane



"broro183" wrote:


Hi JW,

Is the transfer to Word really needed in this manner?

As Mr Smith says Excel is best as a spreadsheet application not a
database. In saying that, your outline of 500 rows is not large at all.
I would recommend setting your data up so that every column has a
header, selecting the whole range & creating a pivot table (ie [alt + d
+ p]) based on the data (& if required, copying the pivot data into
Word).

Once set up pivot tables are quick ways of manipulating data & the
summarised data could be easily copied in a number of ways eg inserted,
plain text or as a table.


Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=556719

Using Excel as a data base
 

thanks for the feedback - goodluck with your investigations :-)

fyi, the following link has some useful info on pivot-tables:
http://www.peltiertech.com/Excel/Pivots/pivottables.htm
and if you decide that pivots may meet your needs Debra's section has
further tips:
http://www.contextures.com/xlPivot01.html

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=556719