How to find and replace value in textbox in excel using dotnet

Hi All,
Can anyone help me with finding and replacing text inside textbox of excel using dotnet expression
Am using Invoke code activity and am able to enter values in excel cells but unable to replace values in Textbox of Excel … Below is the code am using … Thanks for your help in advance

Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
Dim WB As Microsoft.Office.Interop.Excel.Workbook
Dim WS As Microsoft.Office.Interop.Excel.Worksheet
Dim Textbox As Microsoft.Office.Interop.Excel.Shapes
ExcelApp = New Microsoft.Office.Interop.Excel.Application
WB = ExcelApp.Workbooks.Open(filePath)
WS = CType(WB.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet)
WS.Activate
WS.Range(β€œA1”).Value=CompanyName
WS.Range(β€œE2”).Value=Address
WS.Range(β€œE3”).Value=ZipCode
WS.Range(β€œE4”).Value=Tlf
WS.Range(β€œG3”).Value=VATID
If TeleFax<>β€œβ€ Then
WS.Range(β€œH2”).Value=TeleFax
WS.Range(β€œG2”).Value=β€œTeleFax”
End If
WB = ExcelApp.Workbooks.Open(filePath)
WS = CType(WB.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet)
'Textbox = WS.Shapes.Range(β€œTextBox 1”)

ExcelApp.DisplayAlerts = False
ExcelApp.Visible = True
WB.SaveAs(fileName, FileFormat:=56)
WB.Close

Hello @u322805,

Can you see if this helps?

Why not just do this with regular Activities? Simpler and easier to manage.

Thanks for your reply

Below code worked for me

Try
TextReplacement = Nothing
Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
Dim WB As Microsoft.Office.Interop.Excel.Workbook
Dim WS As Microsoft.Office.Interop.Excel.Worksheet
Dim shp As Microsoft.Office.Interop.Excel.Shape
Dim Old_Str_Text As String
Dim New_Str_Text As String
ExcelApp = New Microsoft.Office.Interop.Excel.Application
WB = ExcelApp.Workbooks.Open(filePath)
WS = CType(WB.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet)
WS.Activate
WS.Range(β€œA1”).Value=CompanyName
WS.Range(β€œE2”).Value=Address
WS.Range(β€œE3”).Value=ZipCode
WS.Range(β€œE4”).Value=Tlf
WS.Range(β€œG3”).Value=VATID
If TeleFax<>β€œβ€ Then
WS.Range(β€œH2”).Value=TeleFax
WS.Range(β€œG2”).Value=β€œTeleFax”
End If
For Each shp In WS.Shapes
Old_Str_Text = shp.TextFrame.Characters.Text
New_Str_Text = Old_Str_Text.Replace(β€œ{Company Name}”, CompanyName).Replace(β€œ{Country}”, CountryName).Replace(β€œ{VAT Text}”, VATText).Replace(β€œ{Month}”, MonthYear)
shp.TextFrame.Characters.Text = New_Str_Text
Next
ExcelApp.DisplayAlerts = False
ExcelApp.Visible = True
TryCast(WB.Sheets(sheetToDelete),Microsoft.Office.Interop.Excel.Worksheet).Delete()
WB.SaveAs(fileName, FileFormat:=51)
WB.Close
Catch e As Exception
TextReplacement = e
End Try

Thanks @postwick for the suggestion, somehow managed to do with VB.net
Much Appreciated

Thanks @AndyMenon … Much Appreciated

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.