?
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
Medium Priority
?
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 19

Accepted Solution

by:
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

   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:Arno Koster
ID: 11988639
you could use

frmDoc.txtValue.Visible = true
0
 
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"
  doevents
wend
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Progress

801 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