Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Go Back  Xtreme Visual Basic Talk > > > Getting values from 3 columns from Excel into 3 arrays


Reply
 
Thread Tools Display Modes
  #1  
Old 02-28-2005, 03:20 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default Getting values from 3 columns from Excel into 3 arrays


Good afternoon!

I've been crawling through these and other forums trying to find a way to get this code to work correctly.

Basically, what I want to do is to open an Excel file, grab the information and loop through it to get an array of information loaded into memory for use in another part of my program. Its ALMOST working, but i just can't quite figure out how to get this last part to work.

Code:
   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'misc declarations
        TextBox1.Clear()
        Dim txtVar() As String
        Dim intLoc() As Integer
        Dim intLen() As Integer

        'initiate Excel stuff
        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWS As Excel.Worksheet

        Try
            'set this to the location that the ASCII_Convert will be located
            xlWS = CType(EXL.Workbooks.Open("C:\test\ASCII_Convert.xls").Worksheets.Item(1), Excel.Worksheet)
            Dim myRange As Excel.Range = xlWS.UsedRange
            Dim row As Integer
            For row = 1 To myRange.Rows.Count
                txtVar(row) = CType(myRange.Cells(row, 1), String)
                intLoc(row) = CType(myRange.Cells(row, 2), Integer)
                intLen(row) = CType(myRange.Cells(row, 3), Integer)
            Next

        Catch ex As Exception
            Throw ex ' <-- Report the error.

        Finally
            If Not xlApp Is Nothing Then
                xlWS = Nothing
                If Not xlWB Is Nothing Then
                    xlWB.Close(SaveChanges:=False)
                    xlWB = Nothing
                End If
                xlApp.Quit()
                xlApp = Nothing
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End If
        End Try
    End Sub
The bolded portion is where my code is erroring out on runtime. I get the following error:

Additional information: Cast from type 'Range' to type 'String' is not valid.

Can anybody give me a hand here? I feel like I'm so close, but the last step I have to take has taken me all day to try to figure out.

Thanks!!!!
Reply With Quote
  #2  
Old 02-28-2005, 06:10 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Yes, the problem is that default properties do not work in .Net when there is no parameter provided. You need to use Range.Value explicitly when you are referring to the Value held by the Range and not the Range Object itself.

Your corrected code should look something like this:
Code:
txtVar(row) = CType(myRange.Cells(row, 1).Value, String) intLoc(row) = CType(myRange.Cells(row, 2).Value, Integer) intLen(row) = CType(myRange.Cells(row, 3).Value, Integer)
-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 03-01-2005, 09:10 AM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Quote:
Originally Posted by Mike_R
Yes, the problem is that default properties do not work in .Net when there is no parameter provided. You need to use Range.Value explicitly when you are referring to the Value held by the Range and not the Range Object itself.

Your corrected code should look something like this:
Code:
txtVar(row) = CType(myRange.Cells(row, 1).Value, String) intLoc(row) = CType(myRange.Cells(row, 2).Value, Integer) intLen(row) = CType(myRange.Cells(row, 3).Value, Integer)
-- Mike

Thanks for the reply! I was fiddling around with the "value" option yesterday, and having the Option Explicit On and putting a .Value there causes it to error while in editting mode (squiggly blue) saying that "Option Explicit on dissallows late binding".

I don't quite know where to go with that.

Thanks for your help!
Reply With Quote
  #4  
Old 03-01-2005, 11:56 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Sorry, my bad...

The problem is that the Range.Cells() method, although returning an Excel.Range object, is actually has it's return type 'As Object'. This means that the compiler cannot know that this object is actually an Excel.Range. So we need to tell it with CType(), and then again to tell the compiler that the Range.Value is a 'String' or 'Integer' respectively. The resulting code is a little ugly:
Code:
txtVar(row) = CType(CType(myRange.Cells(row, 1), Excel.Range).Value, String) intLoc(row) = CType(CType(myRange.Cells(row, 2), Excel.Range).Value, Integer) intLen(row) = CType(CType(myRange.Cells(row, 3), Excel.Range).Value, Integer)
.Net is not always pretty when dealing with legacy VBA.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #5  
Old 03-01-2005, 12:58 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Wonderful! Thanks again.

In the future, where do you think I should turn? The reason I ask is that I'm new to VB.NET and I would like to build applications that would use the "new" style of coding.

I didn't know I was dealing with legacy code.

Hope it works! Thanks again.
Reply With Quote
  #6  
Old 03-01-2005, 01:12 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Excel is a COM-Application, and so is "Legacy" (that is, non .Net) by defination. When you use .Net to control it, you are actually operating through the .Net Interop. And it works pretty well.

The Excel object model was not designed with .Net in mind, so it has some awkward aspects and CType() has to be used with some frequency.

Where to "turn"? I'm not quite sure what you mean... but feel free to come back here with Q's when you get stuck.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 03-01-2005, 01:21 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Bummer... It gives me an error again

Here is what the txtVar line gives me:
Code:
An unhandled exception of type 'System.NullReferenceException' occurred in BBParser.exe Additional information: Object reference not set to an instance of an object.


And thanks for explaining your legacy comment. it makes total sense. Unfortunately, that is not going away, so I guess i just have to deal with it

Last edited by Rogue3; 03-01-2005 at 01:33 PM.
Reply With Quote
  #8  
Old 03-01-2005, 01:35 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

You need to put the following at the very top of your module:
Code:
Option Strict On Option Explicit On
Once you do that, you'll get a squigly line under the 'EXL'. (Oops.) You meant 'xlApp' here.

Once you fix this, I think you'll be fine...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 03-01-2005, 01:55 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Yeah, I had EXL defined outside of the Sub. I changed that, and it gives me the exact same error.

I also added in the Explicit on as well...

I really appreciate your help!
Reply With Quote
  #10  
Old 03-01-2005, 02:14 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Show all your code again, as corrected?
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 03-01-2005, 02:23 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Quote:
Originally Posted by Mike_R
Show all your code again, as corrected?

Code:
Private Sub btnUpdate_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 'misc declarations TextBox1.Clear() Dim txtVar() As String Dim intLoc() As Integer Dim intLen() As Integer Dim count As Integer = 0 Dim line As String = "" Dim MyNew As New StreamReader(FS) 'initiate Excel stuff Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Dim xlWS As Excel.Worksheet Try 'set this to the location that the ASCII_Convert will be located xlWS = CType(xlApp.Workbooks.Open("C:\test\ASCII_Convert.xls").Worksheets.Item(1), Excel.Worksheet) Dim myRange As Excel.Range = xlWS.UsedRange Dim row As Integer 'load the locations and length into memory For row = 1 To myRange.Rows.Count txtVar(row) = CType(CType(myRange.Cells(row, 1), Excel.Range).Value, String) intLoc(row) = CType(CType(myRange.Cells(row, 2), Excel.Range).Value, Integer) intLen(row) = CType(CType(myRange.Cells(row, 3), Excel.Range).Value, Integer) Next 'upload the data to the BBTesting -> For count = 1 To myRange.Rows.Count If line Is Nothing Then Exit For Else line = CType(MyNew.ReadLine(), String) TextBox1.AppendText(line & vbCrLf) End If Next Catch ex As Exception Throw ex ' <-- Report the error. Finally If Not xlApp Is Nothing Then xlWS = Nothing If Not xlWB Is Nothing Then xlWB.Close(SaveChanges:=False) xlWB = Nothing End If xlApp.Quit() xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() End If End Try End Sub
Reply With Quote
  #12  
Old 03-01-2005, 02:47 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

The remaining problem I see is that you have not set your Array's to anything. You need to declare them, giving them an initial size:
Code:
Dim txtVar() As String Dim intLoc() As Integer Dim intLen() As Integer
Theses variables are all 'Nothing' currently.

The problem is that you do not know how big your Array needs to be before hand. So we need to create our Array()'s only after we know how big to make it. Something like this:
Code:
txtVar = New String(myRange.Rows.Count) {} intLoc = New Integer(myRange.Rows.Count) {} intLen = New Integer(myRange.Rows.Count) {}
Then you need to keep in mind that .Net Array()'s are 0-based while Arrays returned by Excel are 1-based. This means we have to index the .Net arrays on a 'row-1' basis:
Code:
For row = 1 To myRange.Rows.Count txtVar(row - 1) = CType(CType(myRange.Cells(row, 1), Excel.Range).Value, String) intLoc(row - 1) = CType(CType(myRange.Cells(row, 2), Excel.Range).Value, Integer) intLen(row - 1) = CType(CType(myRange.Cells(row, 3), Excel.Range).Value, Integer) Next
Putting it all together, I would not wry the following:
Code:
Private Sub btnUpdate_Click_1(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnUpdate.Click 'misc declarations TextBox1.Clear() Dim txtVar() As String Dim intLoc() As Integer Dim intLen() As Integer Dim count As Integer = 0 Dim Line As String = "" Dim MyNew As New StreamReader(FS) 'initiate Excel stuff Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Dim xlWS As Excel.Worksheet Try 'set this to the location that the ASCII_Convert will be located xlWS = CType(xlApp.Workbooks.Open("C:\test\ASCII_Convert.xls").Worksheets.Item(1), Excel.Worksheet) Dim myRange As Excel.Range = xlWS.UsedRange Dim row As Integer 'load the locations and length into memory txtVar = New String(myRange.Rows.Count) {} intLoc = New Integer(myRange.Rows.Count) {} intLen = New Integer(myRange.Rows.Count) {} For row = 1 To myRange.Rows.Count txtVar(row - 1) = CType(CType(myRange.Cells(row, 1), Excel.Range).Value, String) intLoc(row - 1) = CType(CType(myRange.Cells(row, 2), Excel.Range).Value, Integer) intLen(row - 1) = CType(CType(myRange.Cells(row, 3), Excel.Range).Value, Integer) Next 'upload the data to the BBTesting -> For count = 1 To myRange.Rows.Count If Line Is Nothing Then Exit For Else Line = CType(MyNew.ReadLine(), String) TextBox1.AppendText(Line & vbCrLf) End If Next Catch ex As Exception Throw ex ' <-- Report the error. Finally If Not xlApp Is Nothing Then xlWS = Nothing If Not xlWB Is Nothing Then xlWB.Close(SaveChanges:=False) xlWB = Nothing End If xlApp.Quit() xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() End If End Try End Sub
-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #13  
Old 03-01-2005, 03:45 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Thanks alot MikeR

Its working like I want it to, now I can finally get this thing to do what I initially set out to do!

Reply With Quote
  #14  
Old 03-01-2005, 04:02 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 03-02-2005, 12:59 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Well... it works most of the time now.... It seems like every other time I start the application and get it through this code, it bombs out on me. It doesn't point to any code, and just gives me this:

Code:
An unhandled exception of type 'System.NullReferenceException' occurred in Unknown Module. Additional information: Object reference not set to an instance of an object.

And when I click on "Break" it tells me this:

Code:
There is no source code available for the current location

Here is my updated code. I've tried to initialize every object I can think of here, and this error code doesn't help me with the troubleshooting.

Code:
'misc declarations TextBox1.Clear() Dim txtVar() As String Dim intLoc() As Integer Dim intLen() As Integer 'initiate Excel stuff Dim xlApp As New Excel.Application Dim xlWS As New Excel.Worksheet Dim row As Integer = 0 Try 'set this to the location that the ASCII_Convert will be located xlWS = CType(xlApp.Workbooks.Open("C:\test\ASCII_Convert.xls").Worksheets.Item(1), Excel.Worksheet) Dim myRange As Excel.Range = CType(xlWS.UsedRange, Excel.Range) 'load the locations and length into memory txtVar = New String(CType(myRange.Rows.Count, Integer)) {} intLoc = New Integer(CType(myRange.Rows.Count, Integer)) {} intLen = New Integer(CType(myRange.Rows.Count, Integer)) {} For row = 1 To myRange.Rows.Count txtVar(row - 1) = CType(CType(myRange.Cells(row, 1), Excel.Range).Value, String) intLoc(row - 1) = CType(CType(myRange.Cells(row, 2), Excel.Range).Value, Integer) intLen(row - 1) = CType(CType(myRange.Cells(row, 3), Excel.Range).Value, Integer) Next Catch ex As Exception Throw ex ' <-- Report the error. Finally 'clean up If Not xlApp Is Nothing Then xlWS = Nothing 'If Not xlWB Is Nothing Then 'xlWB.Close(SaveChanges:=False) 'xlWB = Nothing 'End If xlApp.Quit() xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() End If 'clean up btnUpdate.Enabled = False txtVar = Nothing intLoc = Nothing intLen = Nothing

Again, everything seems to work just fine about 1/2 the time. The other 1/2, I have to restart the app.

EDIT: Is it possible that I have to initialize the arrays to their values? Possibly causing it to hang when I let them be nothing at the top of my code?
Reply With Quote
  #16  
Old 03-02-2005, 06:40 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

We really need to know what line of code this is, or this is going to be very hard... You need to goto Alt|Build... and change this to Debug. Then run it and you should now be able to know which line fails.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #17  
Old 03-03-2005, 07:40 AM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

I've been running it in Debug mode the whole time. Thats why I'm having so much trouble with this. I think I'll try to step through it one loop at a time.

I think this has to do with the Excel stuff that was added in, as when I look through the variable list if I have a break point set. If I filter down into the Excel.ApplicationClass, I find some of the methods have an error that looks like this:

Code:
COMAddIns <error: an exception of type: {System.Runtime.InteropServices.COMException} occurred> Microsoft.Office.Core.COMAddIns

Now, I set a break point AFTER the loop finished going through the Excel spreadsheet and it didn't give me the unhandled exception as it did above.

Could this be one of the problems? I'm not sure. Will do some more investigation.
Reply With Quote
  #18  
Old 03-03-2005, 11:10 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

You are looking way too deeply into this...

Step through your code... If you cannot, then put MsgBox "1", MsgBox "2", etc. lacing every other line of your code. (Or Debug.Print() statements.) You must figure out which line is failing... or you are lost.

"There is no try, only do." -- Master Yoda
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 03-03-2005, 12:22 PM
Rogue3 Rogue3 is offline
Newcomer
 
Join Date: Feb 2005
Posts: 13
Default

Ok! I stepped through that loop quite a few times, no unhandled exceptions occurred. I even restarted it a few times and did the same thing.

I also "built" the solution and executed it and didn't have that error.

Ghosts? I dunno. I'll continue on with this as is.

Thanks a bunch for your help Mike!
Reply With Quote
  #20  
Old 03-03-2005, 12:52 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Gremlins are always a possibility...

Glad you're on your feet.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
 
Getting values from 3 columns from Excel into 3 arrays
Getting values from 3 columns from Excel into 3 arrays
 
-->