Office 2007 SaveAs Issue

B

Barb Reinhardt

My workbook starts out as a .xls workbook and I have code to check which
version of Excel is being used. If it's version 11 or less, it saves as a
..xls, if it's 12, it saves as a .xlsm. I've just stumbled across an issue
when I use the following code to save a workbook as an XLSM and it has named
ranges that are POP1, POP2, POP3, etc. This will ultimately affect several
hundred different individuals, so I don't want to make an interim solution to
have to change it in several months.

The code I'm using is:
filesavename = Application.GetSaveAsFilename( _
InitialFileName:=SuggName, _
fileFilter:="Excel Files (*.xlsm), *.xlsm")

If filesavename <> "False" Then
ThisWorkbook.SaveAs filesavename
End If

I'm getting no errors regarding the POP1, POP2, POP3 names. Once the
workbook is closed, I can't open it back up again.

If I use the same workbook (freshly opened), turn off events and do a SAVEAS
to a .xlsm workbook, I get a message that reads

The Name, POP1, either conflicts with a valid range reference or is invalid
for Excel. The name has been replaced with _POP1.

I can subsequently reopen the second file, but can't reopen the first.

I've since fixed the name errors, but is there something I can do JUST IN
CASE I have name errors again so that it catches it. Otherwise, we end up
wtih unusable workbook.

Thanks,
Barb Reinhardt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top