0

I have a excel template, where we have different predefined fields like CustomerName, CompanyName, Address etc. All this things are in single cell and this cell is named as "Customer" and similarly we have another cell called "Sender" having fields related to sender details. My job is to find Customer and Sender cells and replace the values that they contain with the actual values, For example i need to find "Customer" Cell and will replace CustomerName with actual name "John", ComanyName with "XYZ" and so on for other fields, same task is for Sender Cell.

I am using Microsoft.Office.Interop.Excel to implement this task as follows.

xlWorkSheet.Cells.Replace("CustomerName", "John", Excel.XlLookAt.xlPart, missingValue, missingValue, missingValue, missingValue, missingValue);

xlWorkSheet.Cells.Replace("CompanyName", "XYZ", Excel.XlLookAt.xlPart, missingValue, missingValue, missingValue, missingValue, missingValue);

The problem is all this fields in a single cell, and i need to find different fields and replace their values, if a fields values is not then shifting the remaining fields up to fill the empty space.

Please provide solution.

ramya
  • 2,350
  • 6
  • 31
  • 57
  • If your template has named ranges then you don't need to use `Replace` to set their values: http://stackoverflow.com/questions/2416727/set-excel-named-ranges-via-c – Tim Williams Oct 26 '12 at 21:45

1 Answers1

0

I would pull the data into a string, do the string manipulation and then set it back to the cell value...

Dim StrToReplace as string = xlWorkSheet.Range("Customer").value

StrToReplace = StrToReplace.replace("CustomerName", "John")
StrToReplace = StrToReplace.replace("CompanyName", "XYZ")
... etc

xlWorkSheet.Range("Customer").value = StrToReplace

You can make it even more efficient by not repeating the StrToReplace.replace on multiple lines such as:

StrToReplace = StrToReplace.replace("CustomerName", "John").replace("CompanyName", "XYZ")

Or, perhaps using a stringbuilder, but this should at least point you in the right direction to solve the current problem.

Hope this helps

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Hi John, Thanks for your quick response, one issue is in the excel cells fields are like this CustomerName \new line CustomerCompanyName \new line AddressLine1 \new line AddressLine2 so when i will copy string in to cells, it would be in a single row instead of as given above, how can we resolve that? – ramya Oct 26 '12 at 21:18
  • I'm not sure I 100% understand what you're saying, but if you want to add linefeeds into the cells, just add in the linefeed to your replace statement. For example: StrToReplace.replace("CustomerName", "John" **& vblf**) – John Bustos Oct 26 '12 at 21:33