Excel Question: Updating a version of a worksheet

Factual Questions
1

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:

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?

2

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).

3

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.

4

Sorry, you are correct, it’s .xlsm.

It doesn’t appear that I have a ‘convert’ option.

Recreating would be an incredible PITA…

5

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?

6

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.

7

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.

8

My good man, that worked!

Thanks!