Getting numbers out of Excel, letting it show up in VB6.

[Qpido]

Hello Forum Members,

I've recently started on a program to calculate the delivery costs, and I've stumbled upon some very frustrating problems.
I read some topics on this site, and I was hoping the experienced people on this forum could help me with this problem.

My problem is the following, I'm trying to get some cells out of Excel and letting it show in lstPrijs, in VB6.
I'm getting this problem:
"The instruction at "0x30a666d5" referenced memory at "0x0065008e". The memory could not be "read".

Click on OK to terminate the program
Click on CANCEL to debug the program"

Excel stays open this way, the code I'm using is:

Sub BelgPo ()

Dim xl As Object
Dim xlsheet As Object
Dim xlwbook As Object

Set xl = CreateObject("Excel.Application")
Set xlwbook = xl.Workbooks.Open("c:\Verzend\Bosman2.xls", , True)
Set xlsheet = xlwbook.Sheets.Item("Belg")

If lstGewicht = "t/m 50kg" And lstPostcode = "10-39" Or lstGewicht = "t/m 50kg" And lstPostcode = "90-99" Then
Me.lstPrijs.AddItem (xlsheet.range("h5").value)
End If

xl.ActiveWorkbook.Close False, "c:\Verzend\Bosman2.xls"
xl.Quit
Set xl = Nothing
Set xlwbook = Nothing
Set xlsheet = Nothing

End Sub

I've tried debugging, but it then says there was a problem with Excel and it will now be terminated. This leaves me to the conclusion that there is a problem with Excel.

I've tried doing something with the ODBC feature, but I don't know what I'm doing wrong.
I don't know how to use the DOA, although I've installed it when I installed Microsoft Query.

The number does in fact show up in lstPrijs, but after the number shows, the error comes. So it must be after that part of the code, I believe.

My second problem is when I've clicked the 50kg and the 10-39, it shows the correct amount in lstPrijs, ofcourse with the error. But then if I click 90-99, it shows all the numbers from the H section in excel, where he's supposed to bring up the same number as the 10-39.

I'm desperate and I hope that someone can help me.

Thanks in advance,

Q~

 

[Qpido]

I fixed the second problem, it was a little mistake.
But the problem with the code still stands.

Q~

 

[tusharm]

What lines generates the error?

Your use of Me.lstPrijs.AddItem implies that the sub you shared is a method in a class module and that lstPrijs.AddItem is a reference to the AddItem method of a variable named lstPrijs that is an object declared within the class module.

Are you instantiating multiple objects of whatever class contains the above sub?

What does AddItem expect?

Have you tried assigning xlsheet.range("h5").value to some temporary variable (probably of type variant) and then using that variable with the AddItem method?

 

[Qpido]

It doesn't say which line, when I do debug it says that Excel has generated errors. Not VB.

AddItem just posts something, it doesn't need to be used.

I want people to select a country in a list, then select the weightclass, then the postal code, and then it should Add something in the lstPrice.

It works when I don't use Excel, but because of constant changes in the prices, it's easier to use Excel with this.

Q~

 

[Legacy 98055]

I did not see any errors in your code. However, this line should be changed...

xl.ActiveWorkbook.Close False, "c:\Verzend\Bosman2.xls"

to

xl.ActiveWorkbook.Close False

There is no need to post a filename if you are not saving changes.

Though I doubt it, this might throw an error when using late binding. VB processes all arguments and does not support short circuiting. Perhaps that line is causing a problem???

If no success after trying the above; use this alternative that retrieves the data from the workbook without actually opening it...

Sub BelgPo()
    
    If lstGewicht = "t/m 50kg" And lstPostcode = "10-39" Or lstGewicht = "t/m 50kg" And lstPostcode = "90-99" Then
        Me.lstPrijs.AddItem FOTRICWB("c:\Verzend\Bosman2.xls", "Belg", "H5")
    End If

End Sub


Function FOTRICWB(ClosedWorkbookFullName As String, _
    SheetName As String, RangeAddress As String) As Variant
    
    Dim conn As Object, rs As Object, SQL As String
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ClosedWorkbookFullName & _
    ";Extended Properties=""Excel 8.0;HDR=NO;"""
    
    SQL = "Select * From [" & SheetName & "$" & RangeAddress & ":" & RangeAddress & "]"
    rs.Open SQL, conn, 1, 3
    FOTRICWB = rs.Fields(0).Value
    rs.Close: conn.Close
End Function

 

[Qpido]

Thanks a bunch Tom!

I got it working and the code you gave me is very helpful!

I now seem to be getting a "Invalid use of Null error.".

Any idea how I could get around this?

Thanks again,

Q~

 

[Legacy 98055]

Add this line before your function returns.

If IsNull(FOTRICWB) Then FOTRICWB = ""

Null is likely the result of a blank cell being read by way of ADO.

 

[Qpido]

Thanks Tom,
It turns out it doesn't seem to understand that there is in fact a number in the Excel cell. It now just gives a blank field in lstPrijs.

I've re-entered the whole data in "Fran" sheet, but it just won't read it.

Have no problems with the other sheets though, even though it's the exact same cell sort.

Q~

 

[Legacy 98055]

Sorry. I don't have a clue.