Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Security in Office 2007 | Excel Discussion (Misc queries) | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Office 2007 compatible with Office 2000 macro programming | Excel Programming | |||
Where in Office 2007 can I record a macro? | Excel Worksheet Functions | |||
Problem sharing spreadsheet between Office10 (Office XP) and Office 11 (Office 2003) | Excel Programming |