DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Urgent: Read excel sheet & compare

  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Urgent: Read excel sheet & compare

    Hi,
    I have to write a program in VB which would read data from several excels kept in a folder and then, the read data needs to be compared against a string. If they match, then I have to ouput that string to a form in VB.

    Can anyone help me on this plzzz? it's kinda urgent!
    :-(

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    How many spreadsheets are you talking about?

    Operating Systems (all of them) have a limit on how many files can be opened at the same time. A better approach would be to open each one individually, grab what you need, put it some where, close the spreadsheet and repeat the process.

    Once you have everything, then do your compare.

  3. #3
    Join Date
    Aug 2005
    Location
    Hermosillo, Sonora, Mexico
    Posts
    103
    I hope this can help you in the part that you have to open and read excel files...
    The code is totally self explanatory, In the load event we're going to open the new instance of the excel library and our excel file “book1.xls” will be accessible from our code. Then we'll use Command1 to retrieve data from book1, please note that you must have some data in the excel file. Similarly Command2 is used to put/replace the data in the excel sheet cells.
    Code:
    'do declare these variables you need to add a reference'to the microsoft excel 'xx' object library. 
    'you need two text boxes and two command buttons'on the form, an excel file in c:\book1.xls 
    
    Dim xl As New Excel.Application
    Dim xlsheet As Excel.Worksheet
    Dim xlwbook As Excel.Workbook 
    
    Private Sub Command1_Click()
    'the benifit of placing numbers in (row, col) is that you'can loop through different directions if required. I could'have used column names like "A1" 'etc. 
    Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1 
    Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2 
    'don't forget to do this or you'll not be able to open'book1.xls again, untill you restart you pc. 
    xl.ActiveWorkbook.Close False, "c:\book1.xls"
    xl.Quit
    End Sub 
    
    Private Sub Command2_Click()
    xlsheet.Cells(2, 1) = Text1.Text
    xlsheet.Cells(2, 2) = Text2.Text
    xlwbook.Save 
    
    'don't forget to do this or you'll not be able to open'book1.xls again, untill you restart you pc. 
    xl.ActiveWorkbook.Close False, "c:\book1.xls"
    xl.Quit
    End Sub 
    
    Private Sub Form_Load()
    Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
    Set xlsheet = xlwbook.Sheets.Item(1)
    End Sub 
    
    Private Sub Form_Unload(Cancel As Integer)
    Set xlwbook = Nothing
    Set xl = Nothing
    End Sub

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    What string do you need to run your compare against, and where is that stored?

Similar Threads

  1. Replies: 7
    Last Post: 02-22-2008, 10:42 AM
  2. renaming excel sheet
    By gsreejit_79 in forum VB Classic
    Replies: 0
    Last Post: 07-22-2006, 02:11 AM
  3. Replies: 0
    Last Post: 03-09-2006, 09:40 PM
  4. Can not pick integer value from excel sheet
    By rajat_sharma69 in forum VB Classic
    Replies: 5
    Last Post: 05-09-2005, 08:40 AM
  5. Replies: 1
    Last Post: 03-27-2002, 01:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center