Search a database and copy content to another file

tristar4ever · Mar 23rd, 2002, 10:10 AM

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

Tom Urtis · Mar 23rd, 2002, 11:03 AM

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.

tristar4ever · Mar 23rd, 2002, 11:56 AM

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

Tom Schreiner · Mar 23rd, 2002, 04:57 PM

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?

tristar4ever · Mar 24th, 2002, 02:42 AM

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

Brian from Maui · Mar 24th, 2002, 03:08 AM

Marcuss,

How's those L-1011 doing? See your user name!

Brian

Tom Schreiner · Mar 24th, 2002, 05:43 AM

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 ]

tristar4ever · Mar 24th, 2002, 06:48 AM

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

Tom Schreiner · Mar 24th, 2002, 07:10 AM

My E-mail address
tstom@hotmail.com

Pse · Mar 24th, 2002, 08:00 AM

Quote:

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

Mar 23rd, 2002, 11:03 AM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,383	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. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 23rd, 2002, 10:10 AM	#1
tristar4ever New Member Join Date: Mar 2002 Posts: 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

Mar 23rd, 2002, 11:56 AM	#3
tristar4ever New Member Join Date: Mar 2002 Posts: 5	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

Mar 23rd, 2002, 04:57 PM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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?

Mar 24th, 2002, 02:42 AM	#5
tristar4ever New Member Join Date: Mar 2002 Posts: 5	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

Mar 24th, 2002, 03:08 AM	#6
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Marcuss, How's those L-1011 doing? See your user name! Brian

Mar 24th, 2002, 05:43 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

Mar 24th, 2002, 06:48 AM	#8
tristar4ever New Member Join Date: Mar 2002 Posts: 5	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

Mar 24th, 2002, 07:10 AM	#9
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	My E-mail address tstom@hotmail.com