Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
259 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is about my first experience with programming Arduino.
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes

618 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