compatibility mode and new file creation

[miconian]

I have an .xls file (compatibility mode) containing about 40 worksheets. My goal is to save each worksheet as a separate workbook. To do this, I turned to David Mcritchie's macro, found here: http://www.mvps.org/dmcritchie/excel/saveas.htm

I run this macro in Excel 2007. It works, but I get the compatibility prompt before each save. As you can imagine, this takes forever and basically defeats the time-saving purpose of using the macro to begin with.

In order to get around this, I have experimented with various combinations of which format (xls, xlsx, xlsm) to use for the original file, and also to designate for the ending of the new files, as shown within the VBA code. This includes saving the original file as .xlsm, closing it, opening it up again, and running the macro then, with the macro either in its original form, or modified to save a different type of file for the new workbooks.

But no matter what I do, the macro only gets as far as creating the very first workbook, and then giving an error that I'm trying to paste into a workbook that doesn't have as many rows as the original workbook. Of course, the new workbook is being created by the macro.

I would very much appreciate suggestions, and would be happy to provide more information, or try different solutions. Thanks!

 

[pboltonchina]

Have you tried disabling alerts

Application.DisplayAlerts = False

Then do your save, then

Application.DisplayAlerts = True

 

[miconian]

I tried adding "Application.DisplayAlerts = False" as the very first line of code in the module, and it had no effect; the alerts still happened. I then saved the workbook as .xlsm, closed it, opened it up, and ran the macro again, still with that line of code added. As before, I got an error that I'm trying to move data to a new worksheet that doesn't have as many rows as the old worksheet. I changed the VBA code so that the new worksheets would be created with the .xlsm extension, but that had no effect; the error still occurred.

 

[dscg]

Try to replace the xlNormal with 52 which is a macro enabled workbook

ActiveWorkbook.SaveAs _
         filename:=xpathname & wkSheetName & ".xls", _
         FileFormat:=52, Password:="", _
         WriteResPassword:="", CreateBackup:=False, _
         ReadOnlyRecommended:=False

Other formats available:
http://msdn.microsoft.com/en-us/library/bb241279(v=office.12).aspx

51 is an xlsx

HTH!

 

[miconian]

I tried it with both 52 and 51, and in each case I tried saving to an xls, and xlsx, and an xlsm, and the result was always the same as before.

According to the debugger, the error is happening on this line:

CurWkbook.Worksheets(wkSheet.Name).Copy Before:=newWkbook.Sheets(1)

 

[dscg]

We should also remove the part that specifies .xls in the filename:

ActiveWorkbook.SaveAs _
         filename:=xpathname & wkSheetName, _
         FileFormat:=52, Password:="", _
         WriteResPassword:="", CreateBackup:=False, _
         ReadOnlyRecommended:=False

Tested on Vista/Excel 2007 and ran w/out a hitch after that correction. Post back the entire code you have if you keep having problems and maybe someone will be able to point out the issue.


'

 

[miconian]

Just tried that. Still getting runtime error 1004 (trying to put data into new workbook with fewer rows than old workbook).

Not sure if it matters, but at this point my source workbook is saved as an xlsm.

 

[miconian]

FYI, it does create the file based on the very first tab, and that file is saved as an .xlsm file in compatibility mode. Apparently the error occurs when trying to put the data into the new file.