View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Margot Kidder Margot Kidder is offline
external usenet poster
 
Posts: 1
Default 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