Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
I save a workbook with workbooks(<filename).close savechanges := TRUE and
any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
Thanks for taking an interest! If I save as the "latest version" then orange
remains orange - But if I save as the old format orange is changed to yellow when I reopen. Do you happen to know how to get the excel version a file was created in ? "Peter T" wrote: Not sure if this is related but I've come across some curious problems linked to the difference in default palettes in pre XL97 and XL97 on. By chance, if you format a colour to orange (right of red on the palette), does it come back a sort of brown after save / close / reopen. Regards, Peter "John Pritchard" <John wrote in message ... I save a workbook with workbooks(<filename).close savechanges := TRUE and any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
Slightly curious, the reason I would have expected the orange brown change
is these are the default colours of color-index 45 in "old" and "new" palettes respectively. Possibly the palette has been customized, or there is another "old" palette I've forgotten about. You might be able to return the file version like this: Sub GetFileFormat() Dim ff, aver, v ff = ActiveWorkbook.FileFormat aver = Array(xlExcel2, xlExcel3, xlExcel4, _ xlExcel5, xlExcel7, xlExcel9795) On Error Resume Next v = Application.WorksheetFunction.Match(ff, aver, 0) If Err.Number 0 Then v = "Later than Excel 97" Else v = Choose(v, "xlExcel2", "xlExcel3", "xlExcel4", _ "xlExcel5", "xlExcel7", "xlExcel9795") End If MsgBox ff & " : " & v, , ActiveWorkbook.Name End Sub Couple of things to try: Reset the palette (tools / options / color) Save / close / reopen Maybe do this a few times, might not work first time, if at all Save the file like this: ActiveWorkbook.SaveAs Filename:="MyName.xls", FileFormat:= xlExcel9795 FWIW, I don't think your formats are changing even though it looks that way, but the default palette displays different colours in different versions (from memory 19 out of the 56 are different). I'd be interested in your feedback. Regards, Peter "John Pritchard" wrote in message ... Thanks for taking an interest! If I save as the "latest version" then orange remains orange - But if I save as the old format orange is changed to yellow when I reopen. Do you happen to know how to get the excel version a file was created in ? "Peter T" wrote: Not sure if this is related but I've come across some curious problems linked to the difference in default palettes in pre XL97 and XL97 on. By chance, if you format a colour to orange (right of red on the palette), does it come back a sort of brown after save / close / reopen. Regards, Peter "John Pritchard" <John wrote in message ... I save a workbook with workbooks(<filename).close savechanges := TRUE and any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
Instead of resetting the palette manually, could you run this macro the
first time, then manually. I'd be interested to know if it errors. Sub ColorsReset() On Error Resume Next ActiveWorkbook.ResetColors MsgBox "Error: " & Err.Number End Sub Error 0 means no error Regards, Peter "Peter T" <peter_t@discussions wrote in message ... Slightly curious, the reason I would have expected the orange brown change is these are the default colours of color-index 45 in "old" and "new" palettes respectively. Possibly the palette has been customized, or there is another "old" palette I've forgotten about. You might be able to return the file version like this: Sub GetFileFormat() Dim ff, aver, v ff = ActiveWorkbook.FileFormat aver = Array(xlExcel2, xlExcel3, xlExcel4, _ xlExcel5, xlExcel7, xlExcel9795) On Error Resume Next v = Application.WorksheetFunction.Match(ff, aver, 0) If Err.Number 0 Then v = "Later than Excel 97" Else v = Choose(v, "xlExcel2", "xlExcel3", "xlExcel4", _ "xlExcel5", "xlExcel7", "xlExcel9795") End If MsgBox ff & " : " & v, , ActiveWorkbook.Name End Sub Couple of things to try: Reset the palette (tools / options / color) Save / close / reopen Maybe do this a few times, might not work first time, if at all Save the file like this: ActiveWorkbook.SaveAs Filename:="MyName.xls", FileFormat:= xlExcel9795 FWIW, I don't think your formats are changing even though it looks that way, but the default palette displays different colours in different versions (from memory 19 out of the 56 are different). I'd be interested in your feedback. Regards, Peter "John Pritchard" wrote in message ... Thanks for taking an interest! If I save as the "latest version" then orange remains orange - But if I save as the old format orange is changed to yellow when I reopen. Do you happen to know how to get the excel version a file was created in ? "Peter T" wrote: Not sure if this is related but I've come across some curious problems linked to the difference in default palettes in pre XL97 and XL97 on. By chance, if you format a colour to orange (right of red on the palette), does it come back a sort of brown after save / close / reopen. Regards, Peter "John Pritchard" <John wrote in message ... I save a workbook with workbooks(<filename).close savechanges := TRUE and any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
Instead of resetting the palette manually, could you run this macro the
first time, then manually. I'd be interested to know if it errors. Sub ColorsReset() On Error Resume Next ActiveWorkbook.ResetColors MsgBox "Error: " & Err.Number End Sub Error 0 means no error Regards, Peter "Peter T" <peter_t@discussions wrote in message ... Slightly curious, the reason I would have expected the orange brown change is these are the default colours of color-index 45 in "old" and "new" palettes respectively. Possibly the palette has been customized, or there is another "old" palette I've forgotten about. You might be able to return the file version like this: Sub GetFileFormat() Dim ff, aver, v ff = ActiveWorkbook.FileFormat aver = Array(xlExcel2, xlExcel3, xlExcel4, _ xlExcel5, xlExcel7, xlExcel9795) On Error Resume Next v = Application.WorksheetFunction.Match(ff, aver, 0) If Err.Number 0 Then v = "Later than Excel 97" Else v = Choose(v, "xlExcel2", "xlExcel3", "xlExcel4", _ "xlExcel5", "xlExcel7", "xlExcel9795") End If MsgBox ff & " : " & v, , ActiveWorkbook.Name End Sub Couple of things to try: Reset the palette (tools / options / color) Save / close / reopen Maybe do this a few times, might not work first time, if at all Save the file like this: ActiveWorkbook.SaveAs Filename:="MyName.xls", FileFormat:= xlExcel9795 FWIW, I don't think your formats are changing even though it looks that way, but the default palette displays different colours in different versions (from memory 19 out of the 56 are different). I'd be interested in your feedback. Regards, Peter "John Pritchard" wrote in message ... Thanks for taking an interest! If I save as the "latest version" then orange remains orange - But if I save as the old format orange is changed to yellow when I reopen. Do you happen to know how to get the excel version a file was created in ? "Peter T" wrote: Not sure if this is related but I've come across some curious problems linked to the difference in default palettes in pre XL97 and XL97 on. By chance, if you format a colour to orange (right of red on the palette), does it come back a sort of brown after save / close / reopen. Regards, Peter "John Pritchard" <John wrote in message ... I save a workbook with workbooks(<filename).close savechanges := TRUE and any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formatting Lost When Saving
I used light orange yesterday sorry. Choosing orange and saving as V4.0
actually changes the orange to red. Running the colorsreset macro first makes no difference to this. The macro message returns 0. I'd actually found the FileFormat parameter just before I saw your reply - I used xlworkbooknormal and explicitly xlexcel4 but with no luck in saving formatting changes. Just tried xlExcel9795 too - again no luck. Happily the people who generate the input file can change to xlExcel5 and this works fine. Thanks for the version macro - works fine - and for all your efforts. "Peter T" wrote: Instead of resetting the palette manually, could you run this macro the first time, then manually. I'd be interested to know if it errors. Sub ColorsReset() On Error Resume Next ActiveWorkbook.ResetColors MsgBox "Error: " & Err.Number End Sub Error 0 means no error Regards, Peter "Peter T" <peter_t@discussions wrote in message ... Slightly curious, the reason I would have expected the orange brown change is these are the default colours of color-index 45 in "old" and "new" palettes respectively. Possibly the palette has been customized, or there is another "old" palette I've forgotten about. You might be able to return the file version like this: Sub GetFileFormat() Dim ff, aver, v ff = ActiveWorkbook.FileFormat aver = Array(xlExcel2, xlExcel3, xlExcel4, _ xlExcel5, xlExcel7, xlExcel9795) On Error Resume Next v = Application.WorksheetFunction.Match(ff, aver, 0) If Err.Number 0 Then v = "Later than Excel 97" Else v = Choose(v, "xlExcel2", "xlExcel3", "xlExcel4", _ "xlExcel5", "xlExcel7", "xlExcel9795") End If MsgBox ff & " : " & v, , ActiveWorkbook.Name End Sub Couple of things to try: Reset the palette (tools / options / color) Save / close / reopen Maybe do this a few times, might not work first time, if at all Save the file like this: ActiveWorkbook.SaveAs Filename:="MyName.xls", FileFormat:= xlExcel9795 FWIW, I don't think your formats are changing even though it looks that way, but the default palette displays different colours in different versions (from memory 19 out of the 56 are different). I'd be interested in your feedback. Regards, Peter "John Pritchard" wrote in message ... Thanks for taking an interest! If I save as the "latest version" then orange remains orange - But if I save as the old format orange is changed to yellow when I reopen. Do you happen to know how to get the excel version a file was created in ? "Peter T" wrote: Not sure if this is related but I've come across some curious problems linked to the difference in default palettes in pre XL97 and XL97 on. By chance, if you format a colour to orange (right of red on the palette), does it come back a sort of brown after save / close / reopen. Regards, Peter "John Pritchard" <John wrote in message ... I save a workbook with workbooks(<filename).close savechanges := TRUE and any cell formatting is lost. I've traced the circumstances to the version of Excel the original <filename was created in. If the file was version 4.0 formatting is lost (even if it's been subsequently saved as the 'latest Excel version') otherwise it's OK. Given that I can have no influence on the Excel version of the original file is there anyway of saving my formatting changes (I'm only interesting in the colorindex). Also how can I tell what the original Excel version was from within VB (or elsewhere) - The above applies to XP, NT and windows 2000. I'm using office 2000 in all cases. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - font/cell formatting lost saving 2003 xls | Excel Discussion (Misc queries) | |||
Why is formatting lost when saving from .xlsx to .xls? | Excel Discussion (Misc queries) | |||
Formatting lost when saving excel 2003 file using excel 2007 | Excel Discussion (Misc queries) | |||
Protected Worksheet, Cell Formatting Lost on Double Click? | Excel Discussion (Misc queries) | |||
Cell formatting changes lost | Excel Discussion (Misc queries) |