Link to home
Create AccountLog in
Avatar of DDB
DDBFlag for United States of America

asked on

how to remove line feed from all cells in all worksheets in excel using vb.NET

Hello: I was wondering if you could provide Vb.Net code. i am trying to remove all line feeds from all cells in all workbooks in excel, if any,  using vb.Net.

attached code does not work, it produced error:

Ms excel cannot find any data to replace. check if your search formatting and criteria are defined correctly. if you sure that matching data exists in workbook, it maybe protected sheet.

but excel is not protected.
 
thank you!
Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

 xlApp = CreateObject("Excel.Application")
            xlBook = xlApp.Workbooks.Open("test.xlsx")

For Each xlSheet In xlBook.Worksheets

 xlSheet.Select()
                    
                    With xlSheet.Cells
                        .Replace(vbCrLf, " ")
                        .Replace(vbCr, " ")
                        .Replace(vbLf, " ")
                    End With
next (xlSheet)

Open in new window

Avatar of FirstSentinel
FirstSentinel
Flag of United States of America image

Substitute in a cell for linefeed seems to work   =Substitute(A1, CHAR(10), "")

Look @ this article

Excel VBA: How To Delete Rows
Avatar of Nasir Razzaq
I have not tested this code but try this


For Each xlSheet In xlBook.Worksheets
 xlSheet.Select()
 For Each xlCell In xlSheet.Celss
     With xlCell
          Value.Replace(vbCrLf, " ")
          Value.Replace(vbCr, " ")
          Value.Replace(vbLf, " ")
     End With
 Next
next (xlSheet)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of DDB

ASKER

the codecruiser code produced error in .net during compilation:
Error      1      'Value' is not declared. It may be inaccessible due to its protection level.      


FirstSentinel, imnorie:

my original code works perfectly as VBA macro. I need to remove those characters so I could be able to load excel in SQl server database.

thank you all for you time.
My bad

change this

Value.Replace(vbCrLf, " ")
          Value.Replace(vbCr, " ")
          Value.Replace(vbLf, " ")

to

.Value.Replace(vbCrLf, " ")
.Value.Replace(vbCr, " ")
.Value.Replace(vbLf, " ")
You may have to do it like this

.Value = .Value.Replace(vbCrLf, " ")
.Value = .Value.Replace(vbCr, " ")
.Value = .Value.Replace(vbLf, " ")
Avatar of DDB

ASKER

the spreadsheet has only  vbLF, somehow it did not work properly because I try to have combination of all possible scenarios, when I removed  
                        .Replace(vbCrLf, " ")
                         .Replace(vbCr, " ")

it did the job. I did not have any problem with original code  when I run it as VBA macro. Kind of puzzled me.

thank you.
Then how did the selected solution help you? You are saying that the cells contained only Line Feed whereas the selected solution said that the cells may not contain Line Feeds!
Avatar of DDB

ASKER

It gave me  a clue to look again at lines that produced the error message.  The  Replace(vbLf, " ")  line did not produce any errors but it did not work with  .Replace(vbCrLf, " ") and  .Replace(vbCr, " "). When I removed first 2 lines the Replace(vbLf, " ") worked ok and gave me result that I expected to have. Although as I said before it's kind of strange that VBA macro did not have any problem at all. I hope I answered on your question.