I will add that the social and phone number fields in the table are both text fields as you would never do math functions to those and as well, when you move a social to excel it often cuts off leading zeros (which valid socials can have up to 2 of them).

I do an autofit to the whole sheet after formatting so the column length is not the issue.

I did try converting the whole column to numerics then back to custom social format and it still functions the same way.


>>I am doing some Excel Automation and I extract out a table, then I do some formatting to some of the columns. I replace the values of the tables with an alltrim on the value from the main table to make sure there are no spaces to mess up the formatting. Some formatting are like a social as ###-##-#### and Phone as (###)###-####. The formatting gets set properly, but it does not show correctly on the excel sheet result. If you click on one of the values and click the cursor on the value in the = box then click somewhere else, it updates the cell showing that value with the correct formatting, but only that cell. If I highlight the whole column and hit F9, it still does not show the values with the correct formatting. Any ideas?
>
>I think the column may be not wide enough. Did you try to AutoFit ? Also did you try to set NumberFormat for the column?
>
>I don't have the same problem with the Excel automation program I have.
>
>

>FUNCTION DBFToExcel
>LPARAMETERS tcXLSFileName, taHeader_Info, tcTitle, tcPassword, tlLockHeaderRow
>EXTERNAL ARRAY taHeader_Info
>* This function assumes, that the output table (cursor) is currently opened
>*-- Generate output to XLS File
>* -- Do some basic parameter checking
>IF EMPTY(ALIAS())
>	RETURN "No table/cursor is currently opened to process."
>ENDIF
>
>IF EMPTY(m.tcXLSFileName)
>	RETURN "Excel file name is not passed."
>ENDIF
>
>IF TYPE("taHeader_Info[1]") <> "C"
>	RETURN "Array taHeader_Info is not passed."
>ENDIF
>
>IF VARTYPE(m.tcTitle) <> "C"
>	RETURN "Excel title is not passed."
>ENDIF
>
>LOCAL loExcel, lnI, loSheet, lnK, lcError, lnLines, ;
>	lnActualFields, lnHeaderCells, lcRange, lcStart, lcEnd, lnCols, lnOffset, ;
>	lnTotalRows, lcStartRow, lcEndRow
>
>lcError = ""
>
>LOCAL ARRAY laTitle[1]
>
>IF NOT EMPTY(m.tcTitle)
>	lnLines = ALINES(laTitle, m.tcTitle)
>	lnOffset = 2
>ELSE
>	lnLines = 0
>	lnOffset = 1
>ENDIF
>
>#include Excel.h
>
>#DEFINE xlPart   2
>
>TRY
>	lnActualFields = FCOUNT()
>	lnHeaderCells = ALEN(taHeader_Info,1)
>	COPY TO (m.tcXLSFileName) TYPE XLS
>
>*-- Create Ole Automation with Excel
>	loExcel = CREATEOBJECT("Excel.Application")
>*-- Open XLS File
>	loExcel.APPLICATION.WorkBooks.OPEN(m.tcXLSFileName)
>
>	loExcel.DisplayAlerts = .F.
>
>	FOR lnI = 1 TO loExcel.APPLICATION.APPLICATION.WorkBooks(1).Sheets.COUNT
>*-- Select individual sheets from open XLS File
>		loSheet = loExcel.APPLICATION.APPLICATION.WorkBooks(1).Sheets(m.lnI)
>
>** Delete the column headers from Excel (first row)
>		loSheet.ROWS("1").DELETE(xlShiftDown)
>
>** Insert lines with Title + 1 or 2 empty rows for the column headers
>		loSheet.ROWS("1:" + ALLTRIM(STR(m.lnLines + m.lnOffset))).INSERT(xlShiftDown)
>
>		IF m.lnLines > 0 && Title font
>			WITH loSheet.RANGE("1:" + ALLTRIM(STR(m.lnLines))).FONT
>				.COLOR = 8388736 && rgb(255,0,0)
>				.SIZE  = 14
>				.Bold  = .T.
>				.NAME = 'Tahoma'
>			ENDWITH
>		ENDIF
>** Header font
>		WITH loSheet.RANGE(ALLTRIM(STR(m.lnLines + 1)) + ;
>				":" + ALLTRIM(STR(m.lnLines + m.lnOffset))).FONT
>*  .Color = Rgb(255,0,0)
>			.SIZE  = 11
>			.Bold  = .T.
>			.NAME = 'Tahoma'
>		ENDWITH
>
>		lnCols = m.lnHeaderCells
>		lcEnd = IIF(m.lnCols > 26, CHR(INT((m.lnCols - 1) / 26) + 64), "") + ;
>			CHR(((m.lnCols - 1) % 26) + 65)
>		lcRange = "A1:" + m.lcEnd + ALLTRIM(STR(m.lnLines))
>		loSheet.RANGE(lcRange).SELECT
>
>		WITH loExcel.SELECTION
>			.HorizontalAlignment = xlCenter
>			.VerticalAlignment = xlBottom
>			.WrapText = .F.
>			.ORIENTATION = 0
>			.AddIndent = .F.
>			.IndentLevel = 0
>			.ShrinkToFit = .F.
>			.ReadingOrder = xlContext
>			.MergeCells = .T.
>		ENDWITH
>
>*!*			With loSheet.Range("1:2").Borders
>*!*			  .Weight = xlMedium
>*!*			  .LineStyle = xlContinuous
>*!*			 Endwith
>		FOR lnK = 1 TO m.lnLines
>			loSheet.Cells(m.lnK, 1).VALUE = laTitle[m.lnK]
>		NEXT
>
>		lnTotalRows = loSheet.UsedRange.ROWS.COUNT
>
>		FOR lnK = 1 TO m.lnHeaderCells
>			loSheet.Cells(m.lnOffset + m.lnLines, m.lnK).VALUE = ;
>				taHeader_Info[m.lnK,1] + ;
>				IIF(EMPTY(taHeader_Info[m.lnK,2]), "", ;
>				CHR(10) + taHeader_Info[m.lnK,2])
>*			loSheet.Cells(3 + m.lnLines,m.lnK).value = taHeader_Info[m.lnK,2]
>
>			IF !EMPTY(taHeader_Info[m.lnK,3]) && There is format information
>				loRange = loSheet.UsedRange.Offset(m.lnLines + m.lnOffset) && we don't want to apply format for header rows
>				loRange.COLUMNS[m.lnK].NumberFormat = taHeader_Info[m.lnK,3]
>			ENDIF
>
>			IF !EMPTY(taHeader_Info[m.lnK,4]) && There is Column Width
>				loSheet.COLUMNS[m.lnK].SELECT
>				loSheet.COLUMNS[m.lnK].COLUMNWIDTH = taHeader_Info[m.lnK,4]
>			ELSE
>				loSheet.COLUMNS[m.lnK].AUTOFIT()
>			ENDIF
>
>			IF TYPE("taHeader_Info[m.lnK,5]")="C" AND !EMPTY(taHeader_Info[m.lnK,5]) && There is a formula or text cell
>				lcEnd = IIF(m.lnK > 26, CHR(INT((m.lnK - 1) / 26) + 64), "") + ;
>					CHR(((m.lnK - 1) % 26) + 65)
>
>				lcStartRow = ALLTRIM(STR(m.lnLines + m.lnOffset + 1))
>
>				lcEndRow = ALLTRIM(STR(m.lnTotalRows))
>
>				DO CASE
>
>				CASE taHeader_Info[m.lnK,5] = "TEXT"
>					loSheet.Cells(lnTotalRows + 2,m.lnK).VALUE = "TOTALS:"
>					loSheet.Cells(lnTotalRows + 2,m.lnK).FONT.Bold  = .T.
>					loSheet.Cells(lnTotalRows + 2,m.lnK).FONT.NAME = 'Tahoma'
>
>				CASE taHeader_Info[m.lnK,5] ="SUM"
>					loSheet.Cells(lnTotalRows + 2,m.lnK).formula = "=SUM(" + lcEnd + lcStartRow + ":" + lcEnd + lcEndRow + ")"
>					IF !EMPTY(taHeader_Info[m.lnK,3]) && There is format information
>						loSheet.Cells(lnTotalRows + 2,m.lnK).NumberFormat = taHeader_Info[m.lnK,3]
>					ENDIF
>
>				CASE taHeader_Info[m.lnK,5] ="COUNT"
>					loSheet.Cells(lnTotalRows + 2,m.lnK).formula = "=COUNTA(" + lcEnd + lcStartRow + ":" + lcEnd + lcEndRow + ")"
>					IF !EMPTY(taHeader_Info[m.lnK,3]) && There is format information
>						loSheet.Cells(lnTotalRows + 2,m.lnK).NumberFormat = taHeader_Info[m.lnK,3]
>					ENDIF
>
>				ENDCASE
>			ENDIF
>		NEXT
>	NEXT
>
>	IF m.lnHeaderCells > m.lnActualFields
>		lnCols = m.lnActualFields + 1
>** Code from Sergey Berezniker
>		lcStart = IIF(m.lnCols>26, CHR(INT((m.lnCols - 1) / 26) + 64), "") + ;
>			CHR(((m.lnCols - 1) % 26) + 65)
>		lnCols = m.lnHeaderCells
>		lcEnd = IIF(m.lnCols > 26, CHR(INT((m.lnCols - 1) / 26) + 64), "") + ;
>			CHR(((m.lnCols - 1) % 26) + 65)
>		lcRange = m.lcStart + ALLTRIM(STR(m.lnLines + m.lnOffset)) + ":" + ;
>			M.lcEnd + ALLTRIM(STR(m.lnLines + m.lnOffset))
>
>		WITH loSheet.RANGE(m.lcRange).Interior
>			.ColorIndex = 33 && Blue Color
>			.PATTERN = xlSolid
>			.PatternColorIndex = xlAutomatic
>		ENDWITH
>	ELSE
>		lnCols = m.lnActualFields
>		lcEnd = IIF(m.lnCols > 26, CHR(INT((m.lnCols - 1) / 26) + 64), "") + ;
>			CHR(((m.lnCols - 1) % 26) + 65)
>	ENDIF
>
>** Made the Totals row in bold and Green highlight
>	loExcel.RANGE([A1], loExcel.SELECTION.SpecialCells(xlLastCell)).SELECT
>	loExcel.SELECTION.FormatConditions.DELETE
>	loExcel.SELECTION.FormatConditions.ADD(xlExpression,, '=UPPER(Left($A1,5))="TOTAL"')
>
>	WITH loExcel.SELECTION.FormatConditions(1)
>		.FONT.Bold = .T.
>		.Interior.ColorIndex = 4
>	ENDWITH
>
>** Now apply borders to each header cell
>	lcRange = "A" + ALLTRIM(STR(m.lnLines + m.lnOffset)) + ":" + ;
>		M.lcEnd + ALLTRIM(STR(m.lnLines + m.lnOffset))
>
>	WITH loSheet.RANGE(m.lcRange)
>		WITH .BORDERS(xlEdgeLeft)
>			.LineStyle = xlContinuous
>			.Weight = xlThin
>			.ColorIndex = xlAutomatic
>		ENDWITH
>		WITH .BORDERS(xlEdgeTop)
>			.LineStyle = xlContinuous
>			.Weight = xlThin
>			.ColorIndex = xlAutomatic
>		ENDWITH
>		WITH .BORDERS(xlEdgeBottom)
>			.LineStyle = xlContinuous
>			.Weight = xlThin
>			.ColorIndex = xlAutomatic
>		ENDWITH
>		WITH .BORDERS(xlEdgeRight)
>			.LineStyle = xlContinuous
>			.Weight = xlThin
>			.ColorIndex = xlAutomatic
>		ENDWITH
>		IF ALEN(taHeader_Info,1) > 1
>			WITH .BORDERS(xlInsideVertical)
>				.LineStyle = xlContinuous
>				.Weight = xlThin
>				.ColorIndex = xlAutomatic
>			ENDWITH
>		ENDIF
>	ENDWITH
>
>	IF m.tlLockHeaderRow && we need to prevent headers from modifying
>** Code from Borislav Borissov
>		loSheet.UsedRange.SELECT
>		loExcel.SELECTION.Locked = .F. && First we need to UNLOCK all cells
>		loExcel.ROWS(m.lnLines + m.lnOffset).SELECT && Select the header row of the sheet
>		loExcel.SELECTION.Locked = .T. && Lock Cells in the header row
>		loExcel.ActiveWorkbook.ActiveSheet.PROTECT(,.T.,,.T.)
>	ENDIF
>
>	loExcel.RANGE([A1]).SELECT && So we would not end up with whole file selected
>	IF NOT EMPTY(m.tcPassword)
>** Works in newer versions of Word
>		loExcel.ActiveWorkbook.PASSWORD = m.tcPassword && Works in Excel 2003
>	ENDIF
>
>	loExcel.SAVE()
>
>CATCH TO loError
>	lcError = Log_Error(m.loError)
>FINALLY
>	IF VARTYPE(m.loExcel) = 'O'
>		loExcel.QUIT
>		RELEASE loExcel
>	ENDIF
>ENDTRY
>
>RETURN m.lcError
>ENDFUNC
>