locked
Pulling Data from Excel Into VB2005 Application RRS feed

  • Question

  • I want to know if this is possible or if there is some other suggestion on how to get this accomplished. I have an excel spreadsheet that is updated manually 2 - 3 times a day. I want to create a VB application that allows the user to select an event. Under that event, they can pick one or more assets they want to use. After picking that information, the program needs to check to see if there might be a collision based on the data in the spreadsheet. I need to somehow store this spreadsheet information so that I can do this comparison. In addition, you'll notice in the spreadsheet layout that the data consist of multiple items separated by commas. I need to only look at the letters to determine if they will collide.
    Example:
    Let's say the user picks Event 1 and Event 3. They want to use Asset 1 and Asset 3. They can use Asset 1 because Event 1 & Event 3 won't collide but they can't use Asset 3 because they both would be using XA, JR, & CB
    The excel spreadsheet would have Event 1, Event 2, Event 3 as headers for column A,B, & C
    Each event would then have multiple rows for the Assets.  Asset 1 on row 2, Asset 2 on row 3, Asset 3 on row 4, etc.
    Asset 1 under event 1 would have XA-5, XB-1, XC-3, XD-1
    Asset 1 under event 2 would have JA-1, AB-2, XA-5, XB-1
    Asset 1 under event 3 would have AB-1, JA-1, AB-2, WD-3
    Asset 3 under event 1 would have XA-5, JR-2, CB-1, XD-1
    Asset 3 under event 3 would have XA-5, JR-2, LX-1, CB-2
    Is this possible to do???? Any suggestions are welcomed!!!!

    Thursday, March 27, 2008 8:49 PM

Answers

  • Does each entry begin with two letters?  If so then you can just look at the first two charcters of a string like this...

     

    Dim value as String = "XA-1"

    Dim Letters as String = value.Substring(0,2)

     

    ...and then you would use the Letters variable to do comparisons with.  If the values are more complex then provide a full range of such values and maybe how you need to compare them if that is puzzling you as well.

     

    Do you need to store the row/column location of the values?  If so then you can store each value into a datatable object that can retain the row & column position.  This can give you the flexibility to work with the values without having to reference the excel sheet constantly.  In fact, unless you need to keep reading and writing to the spreadsheet it may be better for you to read the data in once, close the workbook object then re-open it to do any writing (hopefully all at once) so that you don't keep a COM object open any longer than you need to.

    Monday, March 31, 2008 2:14 PM

All replies

  • Sure, this all seems pretty reasonable.  What part(s) of this do you specifically need help with?   Are you familiar with opening an Excel workbook through COM interop?  That's the way you can read data from a spreadsheet.  Are you wondering how to apply the logic behind the asset/event evaluations?  And/or are you wondering about how to set up the UI of the application for this?  Let us know what you have done so far and offer some more specific questions and I'm sure we can see you through this project.

     

    Thursday, March 27, 2008 10:51 PM
  • I have figured out how to pull the excel spreadsheet in.  The problem I have now is with the data itself.  Example:

    I can pull the row, column that I need but in each row, column the data will look like this:  XA-1, FG-3, XD-2, SB-1

    I need to extract each of the letters only & not the numbers to do a comparison.

     

    Monday, March 31, 2008 1:27 PM
  • Does each entry begin with two letters?  If so then you can just look at the first two charcters of a string like this...

     

    Dim value as String = "XA-1"

    Dim Letters as String = value.Substring(0,2)

     

    ...and then you would use the Letters variable to do comparisons with.  If the values are more complex then provide a full range of such values and maybe how you need to compare them if that is puzzling you as well.

     

    Do you need to store the row/column location of the values?  If so then you can store each value into a datatable object that can retain the row & column position.  This can give you the flexibility to work with the values without having to reference the excel sheet constantly.  In fact, unless you need to keep reading and writing to the spreadsheet it may be better for you to read the data in once, close the workbook object then re-open it to do any writing (hopefully all at once) so that you don't keep a COM object open any longer than you need to.

    Monday, March 31, 2008 2:14 PM