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

Help Studio
, , , , , ,
1

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

2

Hello @u322805,

Can you see if this helps?

3

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

4

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

5

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

6

Thanks @AndyMenon … Much Appreciated

1 Like
7

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