Solved

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
6
232 Views
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?
0
Comment
Question by:mike93alfa
  • 3
6 Comments
 
LVL 19

Accepted Solution

by:
akoster earned 350 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

   xl.workbooks.Close
   xl.Quit
   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.
0
 

Author Comment

by:mike93alfa
ID: 11964093
hi

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
frmDoc.Refresh
Set objExcel = Nothing
Set objWorkBook = Nothing
Set objWorkSheet = Nothing

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

Expert Comment

by:akoster
ID: 11988639
you could use

frmDoc.txtValue.Visible = true
0
 
LVL 19

Expert Comment

by:akoster
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"
  doevents
wend
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

808 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