Save pop up in excel

Help Studio
, ,
1

Hi All,
i need to do vookup in a excel .I have used invoke code activity. It works fine in development enviroment. but in production excel pop-up come “Do u want to save the file” .PFA the code in invoke code.
Try
Dim app As Microsoft.Office.Interop.Excel.Application
Dim workbook As Microsoft.Office.Interop.Excel.Workbook
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet
app = New Microsoft.Office.Interop.Excel.ApplicationClass
workbook= app.Workbooks.Open(ConsolidatedFilePath)

worksheet = CType(workbook.Worksheets(“Sheet1”), Microsoft.Office.Interop.Excel.Worksheet)

Dim rows_Count As Integer = worksheet.UsedRange.Rows.Count

For i As Integer = 2 To rows_count

worksheet.Range(“F” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,2,FALSE)”
worksheet.Range(“E” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,1,FALSE)”
worksheet.Range(“G” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,3,FALSE)”
worksheet.Range(“H” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,5,FALSE)”
worksheet.Range(“I” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,4,FALSE)”
worksheet.Range(“J” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,6,FALSE)”
worksheet.Range(“K” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,7,FALSE)”
worksheet.Range(“L” + i.ToString).FormulaLocal =“=VLOOKUP(Sheet1!D”+i.ToString+“,Sheet2!A:H,8,FALSE)”

Next
app.DisplayAlerts = False
workbook.Save
workbook.Close

worksheet = Nothing

workbook.Close

workbook = Nothing
app.Quit
app = Nothing

Catch ex As Exception
End Try

please help

@ppr please help

2

@tharani.natarajan

Please add the display alerts off to the code

app.DisplayAlerts = False

and at the end of your code and in catch add app.Displayalerts = True again after closing excel

You can as well turn of screenupdating if needed app.ScreenUpdating = True/False

cheers

3

Thanks for the reply .after saving excel do i need to put app.DisplayAlerts or before saving?

4

@tharani.natarajan

At start of code set it to false before doing any thing…

And after closing and all again set it back to true

Try
Dim app As Microsoft.Office.Interop.Excel.Application
Dim workbook As Microsoft.Office.Interop.Excel.Workbook
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet
app = New Microsoft.Office.Interop.Excel.ApplicationClass
App.DisplayAlerts = False

App.DisplayAlerts = True
workbook = Nothing
app.Quit
app = Nothing

Catch ex As Exception
App.DisplayAlerts = True
End Try

Cheers