Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Search a database and copy content to another file

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Marcuss,

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

    Brian

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My E-mail address
    tstom@hotmail.com

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •