# Excel Question: Updating a version of a worksheet **URL:** \ **Category:** Factual Questions\ **Created:** [March 12, 2013, 5:58pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734 "2013-03-12T17:58:54Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\ **Post date:** [March 12, 2013, 5:58pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/1 "2013-03-12T17:58:54Z")
I wasn’t sure how to title this, but here’s the situation: At my job we’ve been using a worksheet since 2003. People update it and then save it in the latest format (xlsx). The problem I’m having is that when I run a macro, everytime I run it the following comes up: > [@](#): > > **Minor Loss of Fidelity** > > Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. My google-fu is seriously failing me. I want to get rid of these occurrences so that I don’t have to click ‘continue’ on the compatibility checker ever time I run it. I think I should be able to somehow convert all the cells/styles into 2010, instead of recreating the worksheet from scratch - right? Anyone know? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [March 12, 2013, 6:14pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/2 "2013-03-12T18:14:18Z")
Are you sure users are really saving it in 2010 format, and not just changing the extension to .xlsx? I’d think a true Excel 2010 workbook wouldn’t let you run macros. You’d have to save it as a macro-enabled workbook (.xlsm). ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [March 12, 2013, 6:22pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/3 "2013-03-12T18:22:53Z")
Either way, go to the File/Info menu. If you have a Convert option at the top, click that and it should resolve your issues. If there’s no Convert option, I think recreating the worksheet might be easier than futzing with it. ---
**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\ **Post date:** [March 12, 2013, 6:39pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/4 "2013-03-12T18:39:08Z")
> [@TroutMan](#): > > Are you sure users are really saving it in 2010 format, and not just changing the extension to .xlsx? I’d think a true Excel 2010 workbook wouldn’t let you run macros. You’d have to save it as a macro-enabled workbook (.xlsm). Sorry, you are correct, it’s .xlsm. It doesn’t appear that I have a ‘convert’ option. Recreating would be an incredible PITA… ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [March 12, 2013, 6:53pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/5 "2013-03-12T18:53:27Z")
OK, it sounds like the workbook itself was converted, but there is probably some code in the macro that is trying to do some formatting or other operation that is incompatible with 2010. Can you tell at what point in the macro this is happening and paste the code snippet in a reply? ---
**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\ **Post date:** [March 12, 2013, 7:02pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/6 "2013-03-12T19:02:57Z")
I’m not all that familiar with Macros, but the code isn’t very big - I’m just going to edit the names: Sub merging() ’ ’ merging Macro ’ ’ ’ Keyboard Shortcut: Ctrl+m ’ Dim i, filenm i = 2 Do While (Range(“A” & i).Value \<\> “”) Workbooks.Open filename:=“Location\X.xlsm” Windows(“usedtemplate.xlsm”).Activate filenm = Range(“A” & i).Value Range("[X.xlsm]PAF!E16:G16").Value = Range(“E” & i).Value Range("[X.xlsm]PAF!K16").Value = Range(“F” & i).Value Range("[X.xlsm]PAF!E17:H17").Value = Range(“G” & i).Value Range("[X.xlsm]PAF!K17:L17").Value = Range(“H” & i).Value ``` Range("[X.xlsm]PAF!G22").Value = Range("P" & i).Value Range("[X.xlsm]PAF!N17").Value = Range("I" & i).Value Range("[X.xlsm]PAF!M62").Value = Range("J" & i).Value Range("[X.xlsm]PAF!M63").Value = Range("K" & i).Value Range("[X.xlsm]PAF!M64").Value = Range("L" & i).Value Range("[X.xlsm]PAF!M65").Value = Range("M" & i).Value Range("[X.xlsm]PAF!M66").Value = Range("N" & i).Value Range("[X.xlsm]PAF!F71:I71").Value = Range("O" & i).Value Range("[X.xlsm]PAF!E74:O74").Value = Range("Q" & i).Value Range("[X.xlsm]PAF!C75:O75").Value = Range("R" & i).Value Range("[X.xlsm]PAF!C76:O76").Value = Range("S" & i).Value Range("[X.xlsm]PAF!B77:O77").Value = Range("T" & i).Value Windows("X.xlsm").Activate ActiveWorkbook.SaveAs filename:="BLARGLOCATION\" & filenm & ".xlsm", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close i = i + 1 ``` Loop End Sub It’s the “X” location that has the issues, this is the sheet that has been saved and resaved since 2003ish. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [March 12, 2013, 7:55pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/7 "2013-03-12T19:55:57Z")
I think the xlNormal file format should retain whatever format the workbook is already in so I don’t have great hope for this, but try being specific in the FileFormat parameter and replace **FileFormat:=xlNormal** with **FileFormat:=xlOpenXMLWorkbookMacroEnabled**. ---
**Author:** ![Meatros](https://avatars.discourse-cdn.com/v4/letter/m/b4bc9f/32.png) [@Meatros](https://boards.straightdope.com/u/Meatros)\ **Post date:** [March 12, 2013, 8:12pm UTC](https://boards.straightdope.com/t/excel-question-updating-a-version-of-a-worksheet/652734/8 "2013-03-12T20:12:42Z")
My good man, that worked! Thanks!