VB.NET Update Excel


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: VB.NET Update Excel

  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Unhappy VB.NET Update Excel

    I am trying to use variable in excel cell, my code works only when I put explicit range. Here is sample of code

    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    Dim sSPC_BPC(2) As String
    Dim sBPC(1, 4) As Integer


    'The line of code below does not show any values in excel although when I try to look at this cell oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) in Debug mode I could see value.

    oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) = sSPC_BPC(0)

    ' This code below works OK
    oSheet.range("D5").value = sSPC_BPC(0)

    Appreciate your help in advance!

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

    I don't understand what you are trying to do?

    Is your question on how to use a variable range?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    I am just saying that code
    oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) = sSPC_BPC(0)
    does not work. I did not see any changes in Spreadsheet but I could see that
    ?oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) has value in it.

  4. #4
    Join Date
    Mar 2009
    Posts
    7
    I change my database table to keep cell location as combination of the Col ("A".. , ect) and Row (1,2... etc) instead of indexes and I change code to
    oSheet.Range(Trim(sBPC(0, 2)) & CInt(sBPC(0, 1))).Value = sSPC_BPC(0)
    Have no idea why cell integer indexes did not work.

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by DDB007 View Post
    I change my database table to keep cell location as combination of the Col ("A".. , ect) and Row (1,2... etc) instead of indexes and I change code to
    oSheet.Range(Trim(sBPC(0, 2)) & CInt(sBPC(0, 1))).Value = sSPC_BPC(0)
    Have no idea why cell integer indexes did not work.
    So this does work for you and your problem is solved?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Mar 2009
    Posts
    7
    Yes I solved the problem by using different code, but I am still wondering why cells(i,j) does not work. Is it some kind of mistake on my part or corrupted environment?

  7. #7
    Join Date
    Feb 2004
    Location
    Longueuil, Québec
    Posts
    577
    Note that in all your examples, when you where careful to specify the property .Value, it worked.

    In the cases it did not work, you did not specify the Value property, probably assuming that it was the default property.

    In the ActiveX/COM world, where lives Excel, default properties were a standard.

    In the .NET world, a default property is a rarity. You need to have an indexed property in order to have a default property. Value is not an indexed property, so it does not qualify.

    You are mixing 2 very different world (COM vs .NET), and the debugger sometimes do strange things. Add to that the fact that Excel has been the testing ground for VBA in the old days, it carries with it strange ways of working that are not always what you expect.

    You solved your problem, that is the point.

    But since you were still wondering what happened in the first place (good thing for a programmer to keep trying to understand a problem that was solved by doing something else... that is how we learn), I thought my insight could be helpful.
    Jacques Bourgeois
    JBFI
    http://www3.sympatico.ca/jbfi/homeus.htm

  8. #8
    Join Date
    Mar 2009
    Posts
    7
    Thank you! You replay helped me to understand this case.

Similar Threads

  1. Replies: 0
    Last Post: 03-09-2006, 08:40 PM
  2. Replies: 0
    Last Post: 01-31-2006, 01:07 PM
  3. Replies: 0
    Last Post: 01-25-2006, 01:41 PM
  4. Replies: 0
    Last Post: 05-07-2002, 08:34 PM
  5. A moderate view.
    By Paul Mc in forum .NET
    Replies: 214
    Last Post: 06-01-2001, 07:27 AM

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
  •  
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