Excel formula writing in VB 2

[Dan688]

I'm currently testing a program to add txtNo1 to TxtNo2 and out puting it into Excel, that part is fine.
But i need to make the answer in excel to show when I highlight it as a formula like =sum(b2+c2).

HOW!??

All help is good help

 

[zarkon4]

Use .Formula

 

[dyarwood]

Could you post the relevant part of code?

 

[Dan688]

The code is:

Dim appXL As Excel.Application
Dim waXL As Excel.Sheets
Dim wbXL As Excel.Workbook
Dim wsXL As Excel.Worksheet

txtAnswer.Text = CInt(txtNo1.Text) + CInt(txtNo2.Text)
Set appXL = CreateObject("Excel.Application&quot
Set wbXL = appXL.Workbooks.Open("C:\Sheet1.xls&quot
Set wsXL = wbXL.Sheets("Sheet1&quot
wsXL.Cells(3, 1) = txtNo1.Text
wsXL.Cells(3, 2) = txtNo2.Text

 

[Golom]

Depends on where (i.e. in which cell) you want the formula. You need to set a relative reference so if you want the formula in D2

   oWs.Cells(2,4).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"

 

[dyarwood]

zarkon4 is right use the .Formula

Dim appXL As Excel.Application
Dim waXL As Excel.Sheets
Dim wbXL As Excel.Workbook
Dim wsXL As Excel.Worksheet

txtAnswer.Text = CInt(txtNo1.Text) + CInt(txtNo2.Text)
Set appXL = CreateObject("Excel.Application&quot
Set wbXL = appXL.Workbooks.Open("C:\Sheet1.xls&quot
Set wsXL = wbXL.Sheets("Sheet1&quot
wsXL.Cells(3, 1) = txtNo1.Text
wsXL.Cells(3, 2) = txtNo2.Text
wsXL.Cells(3, 3).Formula = "=A3 & B3"

This should put the formula in there. Assuming your text is going in A3 and B3 and the answer goes into C3.

 

[Dan688]

Thank you all for your help. I used dyarwood code but modified this slightly to out put the correct formula. The only other thing left to as is how to open a blank excel spreadsheet with out calling it from its location:

like --- Set wbXL = appXL.Workbooks.Open("C:\ProgramFiles\Excel&quot

Any Ideas?

 

[dyarwood]

Do you mean that you want to open the workbook but not make it visible?

If so I use this (in VBA but gotta be similar)

Dim wbk As Excel.Application
Set wbk = New Excel.Application

With wbk
.Workbooks.Open Filename:="location of file.xls"
.Visible = False
.Cells(3, 1) = txtNo1.Text
.Cells(3, 2) = txtNo2.Text
.Cells(3, 3).Formula = "=A3 & B3"
.ActiveWorkbook.Save
.Quit
End With

This code should open the spreadsheet then do all the stuff as before but with the workbook not visible. Then the workbook is saved and closed. Again this is for VBA but I am assuming it is the same.

dyarwood

 

[Dan688]

Is there not away to load a blank excel spreadsheet like when you use Start - Run - "Excel"?

 

[dyarwood]

Dim wbk As Excel.Application
Set wbk = New Excel.Application

With wbk
.Workbooks.Add
.Visible = True
.Cells(3, 1) = txtNo1.Text
.Cells(3, 2) = txtNo2.Text
.Cells(3, 3).Formula = "=A3 & B3"

End With

This will add in a new workbook. As it would it you opened Excel and Book1 would open. The book work remain open.

dyarwood

 

[pmrankine]

Does this help?

    Dim objExcelApp As Excel.Application 
    Dim xlsExcelSheet As Excel.Worksheet

    ' Create the Excel application.
    Set objExcelApp = New Excel.Application  
    
    ' Add the Excel spreadsheet.
    objExcelApp.Workbooks.Add
    
    ' Check for later versions.
    If Val(objExcelApp.Application.Version) >= 8 Then
        Set xlsExcelSheet = objExcelApp.Worksheets(1)
    Else
        Set xlsExcelSheet = objExcelApp
    End If

This opens excel as it would be opening from the shortcut.