Search a database and copy content to another file

tristar4ever

New Member
Joined
Mar 22, 2002
Messages
5
Hello all,

I have a large database containing almost 30 columns and 600 rows. I have a problem that maybe you experts can help me solve.

I want to make a search for a specific word in one of the columns, say column C. And for every match with that word I want Excel to copy, not only that cell, but a few more (selectd) cells in the same row as well and create a new file (workbook in .xls format) with the content. Maybe pivottable would be one way?

Problem is that I would like this file to update itself automaticlly. If I change anything in the database, I want the information in the file to change as well. And if I add a row with that same name written in column C, I want it to be added into the file.

A lot of wishes, can anyone of you see a solution in this?

All help appreciated,

Marcus Karlsson
Nova Airlines
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sounds do-able, more info please:

(1) Will the column to search in always be C? Or will it vary from time to time and need to be specified by the user each time?

(2) What specifically will be the range of those "few more (selectd) (sic) cells"? From column A to column E (2 on either side of C), or what?

(3) Do you want to create a new file per word, or per search or what? Why create a new file? How should it be named?

(4) Because you want the file to update itself automatically when a new record (row) is added if column C contains the magic word, I imagine you have one word in mind, or hopefully not too many words, as this specific word will probably need to be hard-coded if you want it to be automatic.

Not sure when I'll be available to assist further on this after today, though many programmers frequent the site and may be available to help as well.

Good luck.
 
Upvote 0
Hey Tom,
Thanks for your interest.
I will try to describe this more carefully.

What I have is a database containing information on flights that our airline make. Column A is departure city, column B is arrival city, C is Departure time, D is arrival time and so on with almost 30 columns.

Therefor, the column B contains all the arrival destinations to where we fly, all of them abbreviated to their three-letter designation code (SFO for San Francisco, MIA for Miami, LAX for Los Angeles and so on). We have something around 30 destinations.

What I want to create is a new Excel-document for each destination where all the flights with the destination i.e. MIA is listed, regardless of departure city.

The info I want to copy into this new workbook is i.e. dates the flight is operated, timetable, number of seats, aircraft type, flight number and so on. Each of these things represents separate coulmns in my database. Not neccessary next to eachother and apporimatley a total of 15 columns need to be included.

The reason why I want to create it in a new document is that I later want to webbase this, and let the destinations look at these and only see their own flights. So a new workbook per search would be fine, and named something like "Flightprogram XXX" where XXX is destination code (same as column B in the search)

The thing about this file updating itself would be that I would not need to update it if we change a timetable for example. I want the file changed at the same moment I change it in the database.

Hope this helps, and again, thanks for your shown interest. If you have any further questions, don't hesitate to contact me. I'm getting gray hairs from my tries to make this work so ALL help is appreciated really.

Best regards,
Marcus Karlsson
Nova Airlines
 
Upvote 0
Hi
I was trying to think of ways to help but some questions keep coming up. Can your destination workbooks be updated and viewed from a remote source at the same time?

Does the data have to be presented as an excel worksheet to the destination viewers?

If not, why not simply update a simple random text file for each destination?

It would be much faster than having the need to update a seperate workbook with every change that fires off an update event?

How exactly will your data be viewed by your destinations?
 
Upvote 0
Hey,
It would be possible to create them in a .txt file as well. The problem for me is to make sure that the content is the right (that the search in the database is performed correctly). What I want is something that can be viewed from the internet.

I want it layed up in the same way that the database sort it with some of the selected columns from Excel side by side on the file and all rows containing i.e. MIA below it.

Thanks for your interest!
Marcus Karlsson
 
Upvote 0
It would be easy to write code that would detect changes in specific cells which would in turn update a seperate destination file.

Still a question for you.

You said you wanted to make this web-based.

When your destinations want to see your updated database, but only the info which applies to that specific destination, how will it be displayed?

On a webpage as a simple line of text?
As an Excel worksheet?

The reason I am asking you is this, there are much faster and less demanding ways of storing information from a database than using seperate workbooks for each and every destination.

I would like to help as this is familiar coding to me, but I really need a better understanding of the options available as far as the end result is concerned...

Tom

P.S.
Just wanted to see if we are on the same page as far as the updating requirements.

From what I gather, if selected information is changed in your database, you want the destination record updated immediately?
This message was edited by TsTom on 2002-03-24 04:46
 
Upvote 0
Hey Tom,
Do you have a mail address and I can send you two sample files of how I would like it to be presented. (one as an extract from the database, and one as how I want the Excel-sheet that will be created in Excel shall look like and that the destinations should be able to view).

Thanks for your interest in helping me, I really appreciate it!

Best regards,
Marcus Karlsson
 
Upvote 0
The thing about this file updating itself would be that I would not need to update it if we change a timetable for example. I want the file changed at the same moment I change it in the database.

If I got it right, you simply need a macro on your database sheet that searches for all flights leaving from XXX (MIA, LAX, etc.) and copies a number of cells for each flight found in a new sheet or a new file (or always the same file named for example Flights MIA).
Then, if you update the database, you would have to run the macro once to have all related sheets (or files) updated as well.
Is that so? If it is, it's an easy task.

- Pse
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top