DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Getting numbers out of Excel via VB6

Thread Solved

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Getting numbers out of Excel via VB6

Oct 2nd, 2007

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:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
If lstCountry = "Belgium" And lstWeight = "till 50kg" And lstPostal = "66-69" Then
lstPrice.AddItem ("€ 47,00")
lstTime.AddItem ("48 hours")
End If
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:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
If lstCountry = "Belgium" And lstWeight = "till 50kg" And lstPostal = "66-69" Then
lstPrice.AddItem ("Sheet.Belg(C3)")
lstTime.AddItem ("Sheet.Belg(C16)")
End If
If lstCountry = "Belgium" And lstWeight = "till 50kg" And lstPostal = "66-69" Then
lstPrice.AddItem ("Sheet.Belg(C3)")
lstTime.AddItem ("Sheet.Belg(C16)")
End If

Last edited by Qpido; Oct 2nd, 2007 at 9:08 am.

•

Join Date: Aug 2007

Posts: 160

Reputation: kb.net is an unknown quantity at this point

Solved Threads: 26

kb.net

Offline

Junior Poster

Re: Getting numbers out of Excel via VB6

Oct 2nd, 2007

Hi,

Check this:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
        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
        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

It is never about the number of languages you know, you either have the logic of programming or you don't ...

Some of the codes I post are collected from different sites during the past couple of years, so I would like to thank them for their help and for enabling me to help.

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Re: Getting numbers out of Excel via VB6

Oct 3rd, 2007

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~

Last edited by Qpido; Oct 3rd, 2007 at 9:06 am.

•

Join Date: Oct 2007

Posts: 147

Reputation: hopalongcassidy is an unknown quantity at this point

Solved Threads: 13

hopalongcassidy hopalongcassidy is offline

Offline

Junior Poster

Re: Getting numbers out of Excel via VB6

Oct 3rd, 2007

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.

•

Join Date: Aug 2007

Posts: 160

Reputation: kb.net is an unknown quantity at this point

Solved Threads: 26

kb.net

Offline

Junior Poster

Re: Getting numbers out of Excel via VB6

Oct 3rd, 2007

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.

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Re: Getting numbers out of Excel via VB6

Oct 4th, 2007

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~

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Re: Getting numbers out of Excel via VB6

Oct 5th, 2007

Here is an example of my code:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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~

Last edited by Qpido; Oct 5th, 2007 at 5:59 am.

•

Join Date: Nov 2006

Posts: 30

Reputation: yello is an unknown quantity at this point

Solved Threads: 4

yello

Offline

Light Poster

Re: Getting numbers out of Excel via VB6

Oct 7th, 2007

•

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

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

www.easyprograming.com
Make Your Programing Easy

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Re: Getting numbers out of Excel via VB6

Oct 9th, 2007

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~

•

Join Date: Oct 2007

Posts: 12

Reputation: Qpido is an unknown quantity at this point

Solved Threads: 0

Qpido

Offline

Newbie Poster

Re: Getting numbers out of Excel via VB6

#10

Oct 9th, 2007

Also a new problem has come up,

Take a look at this code :

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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