Filter results from an Excel sheet?
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Filter results from an Excel sheet?

  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Filter results from an Excel sheet?

    Hi everyone.

    I need to access some Excel data using VB so that I can enter pre-set information, and the results will be filtered, then displayed, based on that information. I'd then like to send my songs, a spreadsheet and the compiled program to certain people that may want to use it, because they need a way to filter the songs, so they only get to see a list based on chosen criterium.

    The compiled code would show a few lists, based on the data in the Excel sheet - Genre could include Acoustic, Club, Rock etc. so one filter would be "Genre". All filters should be available to chose from at all times, with the displayed results being shown as soon as the first filter is used. No tracks should show until that's done.
    E.g. if the user chooses a genre, the program would show all songs associated with that Genre.
    Then they could narrow the search by Speed - e.g. Slow, Medium or Fast so the results of the list would be further narrowed by selecting the speed, so they'd show ONLY songs that matched the chosen genre AND the chosen speed, and so on.

    My questions :
    Is this possible using the Excel sheet or do I have to export the data to a different format?
    Are there any templates available or can somebody point me in the direction of a quick-start quide to VB, please as I haven't programmed since the 90's!

    Thanks,
    Adam

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Filter results from an Excel sheet?

    Maybe an ONLINE spreadsheet, but I'd start with a DB, and use EXCEL (if all user will have it) to SHOW the data
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Dec 2011
    Posts
    6

    Re: Filter results from an Excel sheet?

    Quote Originally Posted by dglienna View Post
    Maybe an ONLINE spreadsheet, but I'd start with a DB, and use EXCEL (if all user will have it) to SHOW the data
    Good point, dglienna.

    I would prefer to write and send out a standalone programme and database, so to save myself time would like to use the Excel sheet merely to build the initial database, which I'd then export into standalone format.

    I've only coded in BASIC but I've heard there are pre-written functions for VB. Does anyone know where the best place to start would be please?

    I suppose I need code to build the database from Excel, then create lists to show the available search criteria to the user, then to retreive and display the results, based on the chosen criteria.

    Any help is appreciated.

    Thanks !

  4. #4
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,722

    Re: Filter results from an Excel sheet?

    That sounds as if you would like to make lists of all the fine and completely copyright-free tracks of music you own and send this list out to your thousands of friends, who will select any number of tracks they'd like you to put on a CD for them, so as you can gladly and without any commercial interest or even friendly reward burn CDs for them.

    That's very generous and big-hearted, and if I was so magnanimous, I'd start over with an Access database and when I have some data entered, I'd use VB6 and ADO data access elements to do the program.

  5. #5
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,782

    Re: Filter results from an Excel sheet?

    If you already have your data in excel you can import that as a new table into access using the build in tools within ms access
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Dec 2011
    Posts
    6

    Re: Filter results from an Excel sheet?

    Quote Originally Posted by WoF View Post
    That sounds as if you would like to make lists of all the fine and completely copyright-free tracks of music you own and send this list out to your thousands of friends, who will select any number of tracks they'd like you to put on a CD for them, so as you can gladly and without any commercial interest or even friendly reward burn CDs for them.

    That's very generous and big-hearted, and if I was so magnanimous, I'd start over with an Access database and when I have some data entered, I'd use VB6 and ADO data access elements to do the program.
    I started a music company supplying music from musicians and composers that have signed contracts with me, to companies that use music as part of their work - tv programme producers, ad agencies etc.

    The number of tracks that have come in has increased and each one has multiple columns of data in Excel. I've read on other forums that Excel (and Access?) can have trouble filtering more than 1000 records (not sure how true this is but I don't want to find out after spending time coding) hence the need for a standalone programme that I can re-compile when the need arises, and that can handle thousands of records.

    So... if I have this right, I export the Excel sheet for use by Access, if Access doesn't have the Issues I read about.
    I then find some routines (is that the right word?) that I can adapt to suit my own needs.

    Is there a library of routines that are downloadable anywhere, or do I have to start from scratch?
    I only chose VB as it sounded like it may be kind of familiar to me, being an old BASIC programmer.

    Thanks

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Filter results from an Excel sheet?

    No problem with thousands of records, might have problems with MILLIONS of records, though.

    SQL Server doesn't have any limits, and the EXPRESS (free) version just throttles simultaneous requests to 500/second or whatever the current limit is. That's when you BUY SQL SERVER from Microsoft. (or use a web host)
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Dec 2011
    Posts
    6

    Re: Filter results from an Excel sheet?

    Hi dglienna.

    This isn't for a web server. It's going to be compiled and sent via email so users can run it on their pcs.

    Any links to vb libraries would be great. I don't know what a good site or bad site would be!

    Also, is it possible to compile for mac users?

    Thanks again.

    Adam
    Last edited by ad_mc; December 20th, 2011 at 10:46 AM. Reason: Spelling ;)

  9. #9
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,782

    Re: Filter results from an Excel sheet?

    Vb does not support any OS other than Windows. Some mac users may be running windows or a windows VM or emulator.

    You will find that many if not most users will not be able to open an email attachment.

    You will also find that if you use Excel then your users will be required to have Excel installed on thier system before your program can run.

    All in all it sounds like a bad idea. Better to use an Access MDB or even a plain old CSV and forget about Excel. Also better to post the install on a web site and email a download link to those who would be interested.
    Always use [code][/code] tags when posting code.

  10. #10
    Join Date
    Dec 2011
    Posts
    6

    Re: Filter results from an Excel sheet?

    Quote Originally Posted by DataMiser View Post
    Vb does not support any OS other than Windows. Some mac users may be running windows or a windows VM or emulator.

    You will find that many if not most users will not be able to open an email attachment.

    You will also find that if you use Excel then your users will be required to have Excel installed on thier system before your program can run.

    All in all it sounds like a bad idea. Better to use an Access MDB or even a plain old CSV and forget about Excel. Also better to post the install on a web site and email a download link to those who would be interested.
    I'm creating some confusion here - I'll only use Excel to creat my initial database. From there, I just need a couple of pointers on where to start learning how to create a vb programme that lets end-users find songs using drop-down lists and show them the results. I'll send the compiled prog and the database over ftp such as wetransfer or similar and have a permanent download link as well.

    Wouldn't users need Access if I used that? "Better to use an Access MDB"

    Regarding macs, could I compile it with a self-extracting emulator ? I know this is another subject but I just wondered.

    Thanks all
    Last edited by ad_mc; December 20th, 2011 at 03:25 PM.

  11. #11
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Filter results from an Excel sheet?

    VB6 has libraries that can open Access files, but you need to know the minimum version so your connection string works. Much better to use SQL Express as a DB.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,782

    Re: Filter results from an Excel sheet?

    If you add a reference to ADO 2.6 your program should work fine on XP and later pcs even if you do not include the MDAC files with your install. Access MDB is a data container and can be accessed using ADO without issue, you just need to include the mdb file in your install.

    As for emulators that work on a Mac I have no clue what is available as I do not use Mac nor do I plan to as imo they are over priced and very limited functionaly due to OS limitations and hardware restrictions. I doubt you could do what you ask and even if you could you would likely have to do it on a MAC and certianly not with VB.
    Always use [code][/code] tags when posting code.

  13. #13
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,874

    Re: Filter results from an Excel sheet?

    Using Windows Azure, you can target many devices, but they'd need Internet Access. You pay as you go, based on usage.

    That would also require VB.Net, as VB6 files probably wouldn't work.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  14. #14
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,722

    Re: Filter results from an Excel sheet?

    The idea behind using Access DB is,
    a) you have MSAcces as a powerful tool to plan and construct your actual database
    b) VB6 has powerful and 100% compatible object libraries (ADO) to work with an MDB (Access database file)

    Propably the SQL EXPRESS David mentioned is also fully compatible with ADO (I suppose so), but I'm not sure what construction tools you have there. Access for instance allows quick and instant changes of table structures, adding a field here and there, helps formulating queries and might even directly import Excel data.

  15. #15
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,782

    Re: Filter results from an Excel sheet?

    In the case of SQL Server, Express or other wise you must install and configure the software on the target machine or another machine on the network. SQL Express is a very good engine but for a program such as this I would use an Access MDB, It will do the job just fine and will be much more simple for the end user as well as using less system resources and disk space.
    Always use [code][/code] tags when posting code.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center