Getting numbers out of Excel via VB6

[Qpido]

I'm currently making a program that gives the person who uses it information about the costs for a shipment.

The transport company changes it numbers quite often, so in Excel the numbers are easier to change. I want to have, let's say, C3 be displayed every time in VB in the listbox, Holland, 500kg and a Postcal code is selected.

Does anyone know how I can achieve this?

Thanks in advance,

Q~

Edit:
Here is a piece of code how it is right now:

If lstCountry = "Belgium" And lstWeight = "till 50kg" And lstPostal = "66-69" Then
lstPrice.AddItem ("€ 47,00")
lstTime.AddItem ("48 hours")
End If

I would like it to be something like this:

If lstCountry = "Belgium" And lstWeight = "till 50kg" And lstPostal = "66-69" Then
lstPrice.AddItem ("Sheet.Belg(C3)")
lstTime.AddItem ("Sheet.Belg(C16)")
End If

[kb.net]

Hi,

Check this:

Dim xl As Object
        Dim xlsheet As Object
        Dim xlwbook As Object
    
        Set xl = CreateObject("Excel.Application")
        Set xlwbook = xl.Workbooks.Open("c:\temp.xls", , True)
        Set xlsheet = xlwbook.Sheets.Item(1)

        Me.List1.additem (xlsheet.range("A17"))

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

[Qpido]

Thanks for the tip.

I got the information filling in the listboxes, can you explain to me what the "Me." is useful for?
Also I get an error message:

"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 is still open in Task Manager though.

Thanks again,

Q~

[hopalongcassidy]

Dear qpido,

From your question, it is not clear what you are looking for. VB is supported in both Excel and in MS Access, although the have different object models. So, are you trying to get data from an en Excel spreadsheet into a listbox in a MS Access Form or vice versa?

By the way, "Me." is like "this->" in C++ or "this." in Java.

[kb.net]

Hi,

I don't know what is generating the error as I tested my code and it worked correctly. Try to debug in order to find where the error is. A for Me. you can remove it where the "Me" word references the form you are currently using and am used for using it.

[Qpido]

I tried putting the code into a new project, where everything is new, but I still got the exact same error.

Just to be clear, I'm trying to take data out of cell H5 in Sheet "Belg" from Bosman2.xls,
then I want it to be printed in lstPrijs.

Could it be that I need to add something to Excel's references?

I read that on some places, but as I'm very new to programming, could someone explain me exactly how one goes about doing this.

Thanks so much,

Q~

[Qpido]

Here is an example of my code:

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 lstLanden = "België" And lstGewicht = "t/m 50kg" And lstPostcode = "10-39" Or lstLanden = "België" And lstGewicht = "t/m 50kg" And lstPostcode = "90-99" Then
Me.lstPrijs.AddItem (sheet.range("h5"))
lstTijdsduur.AddItem ("24 uur")
End If

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

End Sub

I hope I've said enough.

Q~

[yello]

Me.lstPrijs.AddItem (sheet.range("h5"))

Change this to
Me.lstPrijs.AddItem (xlSheet.range("h5").Value)

[Qpido]

I've tried changing it to that, but now it just takes like 3 seconds to load the cell and then I get the same error message as first.

When I try to debug it says there is a problem with Excel and it will be terminated.

Maybe the problem is with Excel?

Any thoughts on this?

Q~

[Qpido]

Also a new problem has come up,

Take a look at this code :

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

Ok, when I've clicked the 50kg and the 10-39, it shows the correct amount in the Price List, 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 getting so frustrated :|!

Q~