Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the format of cells after impoting from Access
rkI am a new user with vision impairment, so it is extremely likely that I
missed the help that has already been offered. For that I apologize and thank you for your input. I was working on a database in Access, but was having a problem because of my vision. It was suggested that I would have better luck working in Excel and since I have worked with it before I decided to try. I exported my worksheet to Excel but when I openned it in Excel I immediately noticed something was very different. It is as though every cell has been "formatted as a mini document, complete with "wrap around". While I can see the usefulness of this, and wouldn't mind being able to figure out how to do that, this is not the place where I want such formating to be. If someone can tell me where I made my mistake and help me get this back working the way a new worksheet would work and look I would be very thankful. Thanks again for your attention. -- Siuan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the format of cells after impoting from Access
I should also have said that in some records is appears as if there is also
some sort of hard return imbedded as there are no cells containing enough information to warrant a wrap around. This makes over half of the worksheet filled with empty space. Thanks again for your attention. -- Siuan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with the format of cells after impoting from Access
If the data has alt-enters (char(10)'s or line feeds), then you'll see square
boxes if the cell isn't formatted to wrap text (select all the cells, format|Cells|alignment tab to toggle wrap text). But there are other characters that can cause those square characters. Saved from a previous post: Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" "," ") '<--what's the new character? If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ------- Sometimes those funny characters don't work in the edit|Find dialog. alt-0010 (or ctrl-j) (aka: alt-enters) work ok. char(13) has never worked for me. ============== But I have no idea what will happen if/when you put your data back into Access. Siuan wrote: I should also have said that in some records is appears as if there is also some sort of hard return imbedded as there are no cells containing enough information to warrant a wrap around. This makes over half of the worksheet filled with empty space. Thanks again for your attention. -- Siuan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i format inserted cells in protected worksheet? | Excel Worksheet Functions | |||
want format cells alignment not format cells font style | Excel Discussion (Misc queries) | |||
Why will my cells not format? | New Users to Excel | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions |