Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Marked Solved
Views: 1726

Oct 2nd, 2007

Getting numbers out of Excel via VB6

Expand Post »

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:

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:

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.

Similar Threads

VB6 - How to write a blank Line after writing each record. in Visual Basic 4 / 5 / 6
vb6-excel in Visual Basic 4 / 5 / 6
getting data from a text file and putting it in an excel file using visual basic 6.0 in Visual Basic 4 / 5 / 6
Open Excel in VB6 in Visual Basic 4 / 5 / 6
Linking Excel up with VB6 in Visual Basic 4 / 5 / 6

Qpido

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

Permalink

Oct 2nd, 2007

Re: Getting numbers out of Excel via VB6

Hi,

Check this:

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

kb.net

Reputation Points: 13

Solved Threads: 29

Junior Poster

Offline

169 posts
since Aug 2007

Permalink

Oct 3rd, 2007

Re: Getting numbers out of Excel via VB6

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.

Qpido

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

Permalink

Oct 3rd, 2007

Re: Getting numbers out of Excel via VB6

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.

hopalongcassidy

Reputation Points: 53

Solved Threads: 13

Junior Poster

Offline

148 posts
since Oct 2007

Permalink

Oct 3rd, 2007

Re: Getting numbers out of Excel via VB6

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.

kb.net

Reputation Points: 13

Solved Threads: 29

Junior Poster

Offline

169 posts
since Aug 2007

Permalink

Oct 4th, 2007

Re: Getting numbers out of Excel via VB6

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

Permalink

Oct 5th, 2007

Re: Getting numbers out of Excel via VB6

Here is an example of my code:

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.

Qpido

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

Permalink

Oct 7th, 2007

Re: Getting numbers out of Excel via VB6

Quote ...

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

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

yello

Reputation Points: 13

Solved Threads: 4

Light Poster

Offline

30 posts
since Nov 2006

Permalink

Oct 9th, 2007

Re: Getting numbers out of Excel via VB6

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

Permalink

Oct 9th, 2007

Re: Getting numbers out of Excel via VB6

Also a new problem has come up,

Take a look at this code :

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

!

Q~

Qpido

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Oct 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Adding text into Crystal Report

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Formula for percentage?

DaniWeb Message

Cancel Changes

User Name
Password