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
202 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to adjust sheet 1 81
seriesUp challenge 7 107
VBA color chart bars 12 66
pairs challenge 5 44
This article will show, step by step, how to integrate R code into a R Sweave document
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now