Solved: run-time save file as 2007

[wolf.stalker]

hi all and thanks for any insight you may have into my issue.

The Process :

i run my app which takes every sheet in a workbook, copies each sheet and then creates it own workbook, saves that workbook as the sheets name. eg, workbook has 2 sheets, Plan_A and Plan_B. when the macro runs, worksheet Plan_A gets coppied to new workbook and then that workbook is now named Plan_A.

under 2003, this worked fine. in fact, i got the code on HOW to do it from here but had to tweak it a bit to fit my needs. my company then upgraded to 2007 and now when it runs, i get a nice "error - 1004" message. "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook..." i think this is because i am in a .xlsm format and when it opens a file to copy too, it does it as .xls (in fact, almost 99% sure). i have tried to get around this by saving the file type as .xlsx but i think the root of my problem is here...

[vba]
ActiveWorkbook.SaveAs _
Filename:=xpathname & wkSheetName & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, password:="", _
WriteResPassword:="", CreateBackup:=False, _
ReadOnlyRecommended:=False 'how we save new workbook with name and stuff

[/vba]

The problem:

how can i chage the above code so that when a new file is first opened (default is .xls), it saves it as file type .xlsx ?

i think the easy solution would be to set Excel so that it always starts off as .xlsx but my company has it locked so that it always creates in .xls. as of right now, there is NO getting around that.

again, thank you for your help. i have been working on this for a few months trying to get my IT group to change it's policy, but it was locked due to OTHER issues (macros) that wouldn't run right in 2007.

[Bob Phillips]

I have just plugged it into some 2007 code and it seems to work for me.

[wolf.stalker]

let me ask you this. when you OPEN excel, does it open in 2007 mode or does it open in 2003 (Compatibility Mode) ?

Mine is the 2003 mode. i am pretty sure this is what is causing my issues.

here is the entire block of code in the FOR loop

[vba]
For Each wkSheet In CurWkbook.Worksheets
If wkSheet.Index < 5 Then
'do nothing
Else
shtcnt(1) = (6)

' Application.StatusBar = shtcnt(1) & "/" & shtcnt(2) & _
' " " & wkSheet.Name ' so we can see whats going on in status bar
wkSheetName = Trim(wkSheet.Name) 'get name of worksheet
wkSheetName = wkSheetName & " " & dtimestamp 'add date stamp to worksheet name
Workbooks.Add ' auto build a new workbook

ActiveWorkbook.SaveAs _
Filename:=xpathname & wkSheetName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, password:="", _
WriteResPassword:="", CreateBackup:=False, _
ReadOnlyRecommended:=False 'how we save new workbook with name and stuff

Set newWkbook = ActiveWorkbook
Application.DisplayAlerts = False
newWkbook.Worksheets("sheet1").Delete ' remove sheet1
On Error Resume Next
newWkbook.Worksheets(wkSheet.Name).Delete
On Error GoTo 0
Application.DisplayAlerts = True

'******************************************************
' this line below is where we error out at.
CurWkbook.Worksheets(wkSheet.Name).Copy before:=newWkbook.Sheets(1)
'******************************************************

For Each ws In Worksheets
ws.Activate
Range("A1").Select
ActiveSheet.Protect
Next ws
ActiveWorkbook.Sheets(1).Select
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next wkSheet
[/vba]

[Bob Phillips]

Do you mean if I double-click Plan A.xlsx? That opens in 2007.

Can you post the original workbook as well?

[wolf.stalker]

Do you mean if I double-click Plan A.xlsx? That opens in 2007.

Can you post the original workbook as well?

i honestly dont think it's the solution is in the workbook. i may not have said this, but this whole procedure worked FINE and perfectly up until we upgraded to 2007. In other words, no issues for about 2 years with this. the ONLY thing that changed was the upgrade to 2007.

Bascily what has happened is that I then took this workbok (Orders Workbook.xls) and saved it as (Orders Workbook.xlxm) to bring it up to 2007 standards. that change right there is what is causing the problem. if i run my macro on the .xls version, it runs fun. when i run it on the .xlsm version, it crashes.

[vba]
ActiveWorkbook.SaveAs _
Filename:=xpathname & wkSheetName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, password:="", _
WriteResPassword:="", CreateBackup:=False, _
ReadOnlyRecommended:=False 'how we save new workbook with name and stuff
[/vba]

i think it has SOMEthing to do with the SaveAs function, but it always error's out here.

[vba]
CurWkbook.Worksheets(wkSheet.Name).Copy before:=newWkbook.Sheets(1)
[/vba]

My guess is becuase if i recall correctly, we are trying to copy/paste a sheet that is some 16,000 columns by +1M rows (2007) into a workbook that's sheets are 256 columns by 65536 rows.

hense my error message...

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook.

[wolf.stalker]

woot! i got it to work :-)

[VBA]
ActiveWorkbook.SaveAs _
Filename:=xpathname & wkSheetName & ".xlsm", _
FileFormat:=52, password:="", _
WriteResPassword:="", CreateBackup:=False, _
ReadOnlyRecommended:=False 'how we save new workbook with name and stuff
[/VBA]

i finally found an article that explains my issue and how to resolve it.

here is the link to the site. the page is titled
Use VBA SaveAs and CheckCompatibility in Excel 2007-2010

this is bascily what i got from it...

These are the main file formats in Excel 2007-2010:

51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

i simply changed my FileFormat to FileFormat:=52 and it works correctly, and save it correctly.

[Bob Phillips]

AS far As I can see all you have done is change the constant name to the constant value. In Ron's article, he says he uses values, not because names do not work, but for 2003 compatibility. As he points out, the fileformat is more necessary in 2007, but you had a fileformat. So, I fail to see why the previous code did not work (and as I said, it works for me).

[wolf.stalker]

AS far As I can see all you have done is change the constant name to the constant value. In Ron's article, he says he uses values, not because names do not work, but for 2003 compatibility. As he points out, the fileformat is more necessary in 2007, but you had a fileformat. So, I fail to see why the previous code did not work (and as I said, it works for me).

lol well, i duno what to say. as i said b4, i changed the filreformt to 52 and i tested it. it worked. closed everything out....tried it again....it worked. case solved. just tried it again... /fail same spot. man i duno.

when you open up excel, does it say
Book1 [Compatiblity Mode] - Microshaft Excel ?

if it does, then you should get the error i was (and now are) getting. oh well, back to the drawing board i guess.

[Bob Phillips]

No it doesn't, because I saved it as a 2007 fileformat.