Office '03 macro not funtioning in '07
 

I have the following:

Sub Create_Purchase_Orders()
Dim PO As Double, Path As String
Path = Worksheets("PO Admin").Range("F5").Value
For counter = 1 To Worksheets("PO Admin").Range("G9").Value
PO = Worksheets("PO Admin").Range("G7").Value + counter
Worksheets("C&T PO FORM").Unprotect "password"
Worksheets("C&T PO FORM").Range("M5").Value = PO
Sheets(Array("C&T PO FORM", "Vendor Info")).Copy
ActiveWorkbook.Protect "password"
SaveName = Path & "/" & PO & " - PO Request"
If Dir(Path, vbDirectory) = "" Then
MkDir Path
End If
ChDir Path
ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlNormal,
CreateBackup:=False
'Reset button macros..

ActiveSheet.Shapes("Button 2").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Insert_New_Line"
ActiveSheet.Shapes("Button 3").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Inc_Row"
ActiveSheet.Shapes("Button 4").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Dec_Row"
'Reprotects the sheet...
Worksheets("C&T PO FORM").Protect "password"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End Sub


In Office '07 I get the following error:
RUN TIME ERROR 1004 method saveas of object workbook failed

When I click Debug, highlighted is the line "ActiveWorkbook.SaveAs..."

After some research, I tried changing FileFormat to 52, and the
subsequent .xls references to .xlms. I get the same result.

So...
1. Were the changes I made correct or errant?
2. Is there something else that needs to be done to make this script
work correctly in '07

Thanks,

JT

Office '03 macro not funtioning in '07
 

Do you have the file extensions hidden for known file types? If not, you
will need to include the file extension in your SaveAs file name as ".xlsx"
or ".xlsm", etc.

"Margot Kidder" wrote:

I have the following:

Sub Create_Purchase_Orders()
Dim PO As Double, Path As String
Path = Worksheets("PO Admin").Range("F5").Value
For counter = 1 To Worksheets("PO Admin").Range("G9").Value
PO = Worksheets("PO Admin").Range("G7").Value + counter
Worksheets("C&T PO FORM").Unprotect "password"
Worksheets("C&T PO FORM").Range("M5").Value = PO
Sheets(Array("C&T PO FORM", "Vendor Info")).Copy
ActiveWorkbook.Protect "password"
SaveName = Path & "/" & PO & " - PO Request"
If Dir(Path, vbDirectory) = "" Then
MkDir Path
End If
ChDir Path
ActiveWorkbook.SaveAs Filename:=SaveName, FileFormat:=xlNormal,
CreateBackup:=False
'Reset button macros..

ActiveSheet.Shapes("Button 2").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Insert_New_Line"
ActiveSheet.Shapes("Button 3").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Inc_Row"
ActiveSheet.Shapes("Button 4").Select
Selection.OnAction = "'" & PO & " - PO Request.xls" & "'!
Sheet2.Dec_Row"
'Reprotects the sheet...
Worksheets("C&T PO FORM").Protect "password"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End Sub


In Office '07 I get the following error:
RUN TIME ERROR 1004 method saveas of object workbook failed

When I click Debug, highlighted is the line "ActiveWorkbook.SaveAs..."

After some research, I tried changing FileFormat to 52, and the
subsequent .xls references to .xlms. I get the same result.

So...
1. Were the changes I made correct or errant?
2. Is there something else that needs to be done to make this script
work correctly in '07

Thanks,

JT