+ Reply to Thread
Results 1 to 19 of 19

Import, 2 X pop up box & consolidate

  1. #1
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Import, 2 X pop up box & consolidate

    Hi all,

    Hope you can help me out.. I am hoping I can get some assistance with creating a macro to ask a user to select a work book to import and then it will only had the 1st 2 work sheets from the selected workbook.

    Then it will ask the user what name it wants to find.... then user inputs the name and the macro searches the 2 newly imported worksheets in column A and B, and if it 'contains' the name then will copy it to a results worksheet (and copy row 2 as a static line from each of the work book to differentiate which sheet it came from)





    1. Import button:

    Action: Pop ups box "please select the workbook you would like to import"

    ....the Macro then Imports document\workbook > adds only worksheet 1 and worksheet 2 from the selected workbook (the workbook that is selected could be anywhere on the users C:\*)



    2. Pop up box2:

    "Enter in the name you want to search for"
    2a. ie: 'Sarah' is entered by the user. The macro finds anything in column 'A' & B on both worksheets that were imported in Step 1 that Contains 'Sarah'.
    2b. The macro should then add the results to a new worksheet titled "Results" in the workbook.



    Note1: if Sarah is found in for example column A row 17 and column B row 17 then only 1 instance of this should be copied to the results page.

    Note2: When the results are copied from worksheet 1 (work sheet 1 is actually the macro page, so when I say worksheet 1 I am referring to the 1st work sheet that was imported) row 2 should be copied also as this is the column headers. The same applies for worksheet 2 as the column headers can be different to worksheet 1's.

    I hope I havent you!


    Thanks in advance guys!!!
    Last edited by SarahPintal; 01-14-2010 at 01:24 AM.

  2. #2
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    This may help...

    Ive added an example document. thanks so much guys
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Hi

    How about adding another sheet to show what the result sheet would contain from this example if say you were looking for Sarah.

    If you are just trying to determine if the user is found, and which sheet contains the info, why don't you simply specify the sheet name rather than importing a heading row???

    rylo

  4. #4
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    Heya, thanks for your reply,


    I have added a results page example - if the user is enters 'sarah'
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Hi

    See how this goes. IT assumes that the current workbook structure exists (ie there is a Macro Page, Sheet2, Sheet3 and Results).

    Please Login or Register  to view this content.
    rylo

  6. #6
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    Thanks,

    I tried though am getting an Runtime eror '1004' Application-defined or object-defined error at this point "Sheets(1).Copy Destination:=ThisWorkbook.Sheets("sheet2").Range("A1")"

    I have the following sheets in the workbook "Macro" "Sheet2" "Sheet3" and "Results"

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Hi

    Attach your working book, and an example file that would be opened and imported and I'll have a look.

    rylo

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,264

    Re: Import, 2 X pop up box & consolidate

    First observation is that sheet may need to be Sheet

  9. #9
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    sure thing rylo.

    I have attached the MACRO masterwoork book and a dummy workbook to use as the document to import.


    thanks
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    Hi Dave,

    I spotted when i was reading the code, I change it though still didnt work ...

  11. #11
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Hi

    A couple of small changes

    Please Login or Register  to view this content.
    See how this goes.

    rylo

  12. #12
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    Thanks a heap, That worked like a charm!

    One thing though, when i imported the actual work book 20,000 rows in 1 work sheet , 5000 in the other, i waited 15 minutes and it was still going so i cancled it. can you think of a way to make it more efficient?

    I was thinking possibly for the macro to check if the Colum A and B in both worksheet (anything below row 2) does not contain the keyword in either of the columns for those rows to be deleted, then the macro code you created can kick in... would that speed it up? is that possible?

  13. #13
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    This is a little strange,

    Depending on what I type as the keyword it either goes very quick or very slow.

    ive added a progress bar and for some of the them (the quick ones) i can see the progress bar moving and for the ones that take forever i dont even see a progress bar. It is brain straining some where.

    Task manager says Excel is not responding, and it is using 99% of the CPU at the same time.

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Hi

    What sort of keywords are you using? Given you say that the keyword determines the speed of things, what are you using?

    Also, for the fast responses, how many items are being returned? Same for the slow ones - is it bringing back a lot of matches?

    rylo

  15. #15
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    Another thought. Do you have any formulas, or things that require calculation in your real data sheets? If so, try turning off the auto calc and see if that speeds things up.

    rylo

  16. #16
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    Same for only 2 returns and the same for 50 ........ it works 95% of the time (fast). ive done about 50 searches, and it only crashes a couple of times on 2 specific words. im going to de-bug further, i will let you all knwo if i can find it.

  17. #17
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    okay, i think it may have been a memory issue as now it is working no matter what i search on.

    Thanks for your help guys, great work as always!

  18. #18
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Import, 2 X pop up box & consolidate

    OK. Don't forget to go back to your original post and mark it a SOLVED.

    rylo

  19. #19
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Import, 2 X pop up box & consolidate

    it is now working! thanks for your help rylo... great work, im so happy


    I think it was a memory issue in the end as i ended up puting in some breakpoints and it was clean... then it worked and as been working since no matter what i search on. It takes around 5 seconds to search through 25000 rows.

    thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1