Loop Through All Excel Cells

[LaCour]

I have created an automated process to export Access queries into .xls files. The problem I am running into is the fields that have character returns come over with an extraneous character in Excel. The unfavorable ASCI (13) shows up like a small box and I can correct it by using the ASCI (10) character.

SUBSTITUTE(Field,CHAR(13),CHAR(10)) will work, but I want to automate this process to do it to every cell.

I need a looping framework to apply the change to every cell in a work book.

If anyone has better ideas, let me know. It would be even nicer if I could manipulate the data in Access, but I think that this happening in the conversion process.

Thank in advance
Blair

 

[VBAjedi]

A simple looping construct (off the top of my head - not tested) is:

Dim C as Range
For Each C in Worksheets("Sheet1").UsedRange.Cells
   C.Value = whatever. . .
Next C

To take it one step further, you can loop through all the sheets in a workbook:

Dim C as Range, WS as Worksheet
For Each WS in Worksheets
   For Each C in WS.UsedRange.Cells
      C.Value = whatever. . .
   Next C
Next WS

Hope that gets you pointed in the right direction!

VBAjedi

 

[PHV]

Have you tried something like this ?
Cells.Replace What:=vbCr, Replacement:=vbLf, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244