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