I put  data into Excel from a VB form but need to return data from Excel into the VB form.

Posted on 2004-09-01
Medium Priority
Last Modified: 2010-04-17
I use VB create a quotation program. The form enable the user to get information such as customer details from an access database. Next the user press a button and all this info is pasted into an excel quotation template. The body of the quote is then done in excel and all the calculations are performed. After this I want the total amount shown in a specific cell on the spreadsheet to be passed back to the open VB form and the quote info (already on the form) and the total amount to be saved into a accel database.

How do I capture the dell value from Excel after the quote is completed?
Question by:mike93alfa
  • 3
LVL 19

Accepted Solution

Arno Koster earned 1400 total points
ID: 11950246
you can open the excel document with a subroutine such as :

Public Sub test()
   Dim xl As Object
   Set xl = CreateObject("Excel.Application")

   xl.Visible = True

   xl.workbooks.Open ("c:\test.xls")
   MsgBox xl.Range("A2").Value

   Set xlapp = Nothing
End Sub

which in turn initialises a new excel instance, makes it visible, opens the file c:\test.xls, and displays the value of the cell A2.
Afterwards the workbook is closed & excel is properly dismissed.

substitute the msgbox line with a line VB_FORM_TEXTBOX = xl.Range("A2").Value and you're done.

with the same symantics you can open access and post the quote information into the database.

Author Comment

ID: 11964093

thanx but i am not sure how to apply what youve suggested. This is what ive done and the part to paste to excel works fine

Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objWorkSheet As Excel.Worksheet

'Create Excel object
    Set objExcel = GetObject("", "Excel.Application")
    Select Case (Index)
    Case 0
        Set objWorkBook = objExcel.Workbooks.Open(App.Path & "\" & "Quotation.xls")
    Case 1
        Set objWorkBook = objExcel.Workbooks.Open(App.Path & "\" & "Invoice.xls")
    Case 2
        Set objWorkBook = objExcel.Workbooks.Open(App.Path & "\" & "Del Note.xls")
    End Select
    objExcel.Visible = True
    frmMain.Visible = False

'Place the data entered into Excel  spreadsheet
    Set objWorkSheet = ActiveSheet
    With objWorkSheet
    .Cells(15, 10) = txtDate.Text
    .Cells(13, 3) = lblDescrip.Caption
    .Cells(19, 9) = Label4.Caption
    .Cells(19, 10) = txtDocNum.Text
    .Cells(15, 5) = cmbCompany.Text
    .Cells(17, 5) = txtContact.Text
    .Cells(19, 5) = Text3.Text
    .Cells(20, 5) = Text4.Text
    .Cells(21, 5) = Text5.Text
    .Cells(23, 3) = txtDocDescrp.Text
    End With
                                        ' everything works well upto here. excel is now open and the user types
                                        ' the quote and a formula in excel calculates the total costs for all the
                                        ' items entered in the quote. The total is stored in cell j43. a macro then
                                        ' prints the quote,saves it and closes excel to show the original VB form. At
                                        ' this point I need a textbox (txtValue) to become visible and capture the value
                                        ' that was stored in j43 on the excel sheet.

frmDoc.txtValue.Text = Range("j43").Value
Set objExcel = Nothing
Set objWorkBook = Nothing
Set objWorkSheet = Nothing

    frmDoc.Text6.Text = Range("j43").Value
    frmMain.Visible = True
    Exit Sub
End Sub
LVL 19

Expert Comment

by:Arno Koster
ID: 11988639
you could use

frmDoc.txtValue.Visible = true
LVL 19

Expert Comment

by:Arno Koster
ID: 12180057
have you succeeded ?

just in case you are struggling on a way to pause macro execution until excel is closed you might try to implement a little excel macro which changes the value of a certain cell (eg. a1)  from "in progress" to "finished" just before the document is closed.

Then add the following code in the form code just before end with :

while .cells(1,1) <> "finished"

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Six Sigma Control Plans
Loops Section Overview

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question