File Save As Macro, Excel 2003 and 2007

[Gerald Higgins]

I've been using Excel 2003 until last week, when I got switched to 2007.
My colleagues are still all on 2003.

I have a macro that has been running fine in 2003, but testing it in 2007 it now falls over.

It does a number of things, but at the point where it falls over, it is saving the single current worksheet of the current multi-sheet workbook, as a standalone file, with a single sheet.

This code works in 2003....

ActiveWorkbook.SaveAs Filename:= "C:\Filename.xls", FileFormat:= xlExcel4

but it falls over in 2007, with an error message of

Run time error '1004'
Method 'Save as' of object '_Workbook' failed

I'm guessing this is because 2007 doesn't like me saving it in xlExcel4 format, and when I try to do it manually, I can't find that option on the Save As menu.

So, QUESTION, does anyone know what code I could use to achieve the same result, and preferably for it to work in BOTH 2003 AND 2007 ?

Thanks in advance for any help . . .

 

[Andrew Poulsom]

Did you see Jon's suggestion?

 

[Gerald Higgins]

Did you see Jon's suggestion?

In post #8 ?
Yes thanks, seen it, not tested it yet.

It will take a while (maybe a few days) for me to test it in both 2003 and 2007, but I'll give it a go.

Thanks again to everyone for their input.

 

[eyong123]

Hi gurus I need urgent help. I wish have a template which wish to use to create a new file for each week. After the user enters the start date of the new week in the template and click on the start new week buton, i will like the sheets to be renamed and then the start new week button dispear and then save the new document based on cell value. After this is done it should close the template as it was in the beginning and open the new workbook for the new week.

I have these codes which seems to do what I want except that in the end the name are inversed, the template gets the name of the new file and vice versa.
I will be very grateful if u can help



Sub StartNewWeek()
'


Application.ScreenUpdating = False 'speed up macro execution
Application.EnableEvents = False 'turn off other macros for now
Application.DisplayAlerts = False 'turn off system messages for now


Sheets("Sheet 1").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 1").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 2").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 2").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 3").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 3").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 4").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 4").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 5").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 5").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 6").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 6").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("Sheet 7").Select
ActiveSheet.Name = Range("A3").Text

Sheets("DB 7").Select
ActiveSheet.Name = "DB " & Left(Range("B30").Text, 5) & ""

Sheets("SET UP").Select

ActiveSheet.Shapes("Rounded Rectangle 281").Select
Selection.Delete

Dim bgExcel As New Excel.Application
Dim wb As Workbook
Dim sourceFile As String
Dim DestFile As String

bgExcel.Visible = False

sourceFile = "C:\EEE\Fab 1& 2 Daily OEE Report TEMPLATE.xls"
DestFile = "C:\EEE\Fab 1& 2 Daily OEE Report WK " & Cells(2, 17).Text & ".xls"

'Look to see if file already exists, don't want to over-write it
If Dir(DestFile) <> "" Then
MSG1 = MsgBox(" The File " + DestFile + " Already Exists", vbYesNo, "Do you want to replace the file ?")
If MSG1 = vbYes Then

Set wb = bgExcel.Workbooks.Add(DestFile)
wb.SaveCopyAs sourceFile
wb.Close SaveChanges:=False
bgExcel.Quit
MsgBox "Copied the file"
Else
Exit Sub
End If

Else
Set wb = bgExcel.Workbooks.Add(sourceFile)
wb.SaveCopyAs sourceFile
wb.Close SaveChanges:=False
bgExcel.Quit
MsgBox "Copied the file"
End If

Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
End Sub