Using the OLE and Excel, how do i get info from excel??

[purdy_wis]

I have an excel file on my form and i have it all set up to make this total up with the part prices and quantities that i enter, it even gives me a grand total. I want to get that total out into a separate text box, how would i do that, could u set it up for me so that i could put it into the commands for a cmd button. that would be very helpful thanks

[JordanChris]

Load data from Excel back into VB:

Private Sub cmdUpdate_Click()
Dim lRow As Long, lCol As Long, dValue As Double, dYear As Integer, dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Dim c As Excel.Range
    Set obExcelApp = CreateObject("Excel.Application")
    Set obWorkBook = obExcelApp.Workbooks.Open("C:\temp\temp.xls")
    For Each c In Excel.ActiveSheet.UsedRange
        dValue = c.Value
        MsgBox dValue ' Just show the data
    Next c    
    Set obWorkBook = Nothing
    Set obExcelApp = Nothing
End Sub

You might not want ALL the data from Excel, so you can cut down the FOR EACH line to something smaller.
MsgBox Excel.ActiveSheet.Range("A6")

[purdy_wis]

that second line with the "Dim obExcelApp As Excel.Application" pops up an error message "Compile Error, User-Defined Type not defined"
what is that supposed to mean?? Thanks for ur help

[JordanChris]

That means you need a reference to Excel.

Project menu - References.
Add Microsoft Excel

[purdy_wis]

hey now i want to get the info out but that isnt really working, this is waht i have for a code now

Dim lRow As Long, lCol As Long, dValue As Double, dYear As Integer, dID As String
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Dim c As Excel.Range
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("C:\work orders\partsused.xls")
txtmaterals.Text = Excel.ActiveCell.Cells = (E12)
Set obWorkBook = Nothing
Set obExcelApp = Nothing


OK then it gives me an error message RUNTIME ERROR 424, OBJECT REQUIRED. it was on this line:
txtmaterals.Text = Excel.ActiveCell.Cells = (E12)

so maybe thats the wrong way to get the info out, right?
Well anyways im sure u know cuz its stumped my for over an hour today so Id really like ur help
thanks

[JordanChris]

Change
txtmaterals.Text = Excel.ActiveCell.Cells = (E12)

to:
txtmaterals.Text = obExcelApp.ActiveCell.Cells = (E12)


Or one of the other Objects if that one isn't correct!

[purdy_wis]

no that one doesnt work

i'll keep working on it
thanks

[purdy_wis]

OK I give up on trying to figure it out myself, ive spent 4 hrs.
So what other options do i have to put in for the other objects??
thanks a whole lot!

[jayakumar]

hi,

txtmaterials.text=obWorkbook.range("E12").value

this will solve u r problem.