CODEWITHSUNDEEP
vb.netexcelformula
mrn
mrn

Reputation: 425

Write formula to excel cell using VB.NET (What is wrong with this code?)

I want to sum a range from a sheet from one excel workbook and write it to another workbook. I came up with a following code but it is not working.

1) it opens a file open windows which asks me to select workbook to update

2) after selecting a workbook it writes "0" as a value.

Code:

Dim xl1 As New Excel.Application
Dim xl2 As New Excel.Application

Dim wb1 As Excel.Workbook
Dim wb2 As Excel.Workbook

Dim st1 As Excel.Worksheet
Dim st2 As Excel.Worksheet

wb1 = xl1.Workbooks.Open("F:\excelsumtest\file1.xlsx")
wb2 = xl2.Workbooks.Open("F:\excelsumtest\file2.xlsx")

st1 = wb1.Worksheets(1)
st2 = wb2.Worksheets(1)

st2.Cells(1, 1).formula = "=Sum(st1!A1:S1)"

Cheers

Upvotes: 0

Views: 4723

Answers (2)

shine
shine

Reputation: 1

This worked for me:

Dim xlsApp As New Microsoft.Office.Interop.Excel.Application 
xlsApp.DisplayAlerts = False

Upvotes: 0

Joseph
Joseph

Reputation: 5160

Try changing this line:

wb2 = xl2.Workbooks.Open("F:\excelsumtest\file2.xlsx")
'to this:
wb2 = xl1.Workbooks.Open("F:\excelsumtest\file2.xlsx")

I'm tying both workbooks to 1 instance of an Excel application. This isn't tested, but I think that this way, Excel will understand that it's the worksheet from the xl1 workbook that you're trying to insert.

If not, you might have to fully define the workbook you're referencing in your formula.

Hope this helps.

Upvotes: 2

Related Questions