There's a couple of key statements you've made:

"I went to update it to work for Excel 2007"

and:
"until I changed ".xls" to ".xlsx""

First of all, is the macro IN the file you're saving?
or is this macro opening another file, modifying and saving it?
because, first of all, Excel2007 doesn't "use" .xls files anymore.
it uses .xlsx, .xlsm, and .xlsb

It can OPEN the earlier .xls files and save them, but
it is doing so in "compatibility mode"
meaning that not all of the 2007 functionality is available in this file.

Your message is the most obvious example.
Your file in 2007 format contains
1,048,576 rows and 16,384 columns

but the .xls format only supports 65,536 rows and 256 columns.

So the "compatibility message" is telling you that the extra cells
will not be saved, and any formulas that reference these cells
will return a #REF! error.
This is actually a very informative message (which is unusual)

--------------------------------------------------------------------
Another BIG thing to consider is that Microsoft created the .xlsx
format so that it would NOT support macros.  Therefore, we can pass
a .xlsx file around without risk of having a hidden macro designed 
to "wreak havoc".  So, if you save the file in .xlsx format, IT WILL
DELETE ALL MACROS!
--------------------------------------------------------------------

Next, you need to recognize that changing the file extension of the file name
does NOT change the file format being saved.
that is:
If your workbook is called Book1.xlsm and you use fso.GetBaseName
you get "Book1", then you use & "xls" or & ".xlsx"
all you've done is created a string called "Book1.xlsx"
You could've used 

TheFileName = fso.GetBaseName(ActiveWorkbook.Name) & ".pdf"

but that doesn't mean Excel is going to save the file as a .pdf FORMAT.
it just means excel is going to save the file in an Excel format and
CALL it Book1.pdf.

That would be the same way as going into your folder and changing all of 
the .xlsx files to .jpg and expect to be able to edit them in MSPaint!

Where Excel deterimines the file format is with the FileFormat parameter
in the SaveAs function.
In Excel97 (office 2003), the .xls format was considered the "normal" format
so,  FileFormat:=xlNormal was used.

So, in your line:

ActiveWorkbook.SaveAs Filename:=TheFileName, FileFormat:=xlNormal

if TheFileName is Book1.xlsx, you're saying:  Save the file in Excel 2003
format, but call it 2007.
That's why, when you try to call it up, there's a problem because Excel expects
to see a 2007 file and it doesn't recognize it as a 2007 format.

If you record a macro and save a file as different formats, you'll find that
the values for FileFormat are:
 xls  :FileFormat:=xlExcel8
 xlsx :FileFormat:=xlOpenXMLWorkbook
 xlsm :FileFormat:=xlOpenXMLWorkbookMacroEnabled
 xlsb :FileFormat:=xlExcel12

So, I would suggest:
If you're saving the file in a different format than the one it is in,
be sure the FileFormat: parameter matches the file extension.

If you're NOT changing the file format, don't use GetBasename,
just use: TheFileName = ActiveWorkbook.Name
and you may be able to drop the FileFormat: parameter altogether.


I did some quick testing.. it looks like if you're NOT changing
the file type, you could simply use:

ActiveWorkbook.SaveAs Filename:=TheFileName

hope this makes sense.

If you need any clarification, let me know.

Paul






________________________________
From: Steve <sspatri...@yahoo.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thu, August 12, 2010 12:43:32 PM
Subject: $$Excel-Macros$$ Save As Excel Workbook Macro

Hi,

I have this macro that someone had helped me with quite a while ago.
It was working fine until I went to update it to work for Excel 2007.

The macro below is what it was until I changed ".xls" to ".xlsx".

'SaveAsExcelWorkbook2()
'Remember to add the reference to Microsoft Scripting Runtime
Dim TheFileName
Dim fso As FileSystemObject
Set fso = New FileSystemObject
TheFileName = fso.GetBaseName(ActiveWorkbook.Name) & ".xls"
TheFileName = Application.GetSaveAsFilename(TheFileName, _
FileFilter:="Microsoft Office Excel Workbook (*.xls),*.xls")

If (TheFileName <> False) Then _
ActiveWorkbook.SaveAs Filename:=TheFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Set fso = Nothing



I also had another part of the macro that I had to change the number
of rows to
like 600K. Anyhow, the changes seem to work until the very end when I
get a
pop-up like the following:

"Compatibility Report for NON POC-FIX.xlsx
Run on 8/11/2010 17:42

The following features in this workbook are not supported by earlier
versions of
Excel. These features may be lost or degraded when you save this
workbook in an
earlier file format.

Significant loss of functionality # of occurrences

This workbook contains data in cells outside of the row and column
limit of the
selected file format. Data beyond 256 (IV) columns by 65,536 rows will
not be
saved. Formula references to data in this region will return a #REF!
error."



Anyhow, I'd like to figure out what is causing the pop-up for
compatibility to show up and figure out why the file that is being
created now is corrupt and won't open.

Thanks,

Steve

-- 
----------------------------------------------------------------------------------

Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com

To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

-- 
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!

We reach over 7000 subscribers worldwide and receive many nice notes about the 
learning and support from the group.Let friends and co-workers know they can 
subscribe to group at http://groups.google.com/group/excel-macros/subscribe

Reply via email to