Can't Open Saved Excel Files

[Bishop]

I had the following code for a 2003 Excel Spreadsheet:

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Microsoft Office Excel Workbook(*.xls),*.xls", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlWorkbookNormal

We have upgraded to Excel 2007 and now everytime I open this workbook and it
saves using the above code I can't re-open it! I've changed the code for the
AUserFile but I don't know how to change the second line of code to make it
work. The changes I made are below:

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlWorkbookNormal

I'm thinking xlWorkbookNormal has to be changed but I don't know to what.

[Bishop]

Ok, turns out the file format has to match the filefilter set so once I set
the file format to the correct setting problem solved. Here's what my code
looks like now:

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlOpenXMLWorkbookMacroEnabled

This are the main formats in Excel 2007 :

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

so just pick whichever applies

Cheers

[Spurfan]

Where is this code located?

Thanks.

[Bishop]

I created that code myself to utilize the GetSaveAsFileName procedure. I was
getting the error message because I was assigning the wrong file format to
match the filefilter setting.

[Spurfan]

I'm sorry Bishop, but you are way over my head. Can you simplify a little?

Thanks.

[Bishop]

Application.GetSaveAsFilename Method

Displays the standard Save As dialog box and gets a file name from the user
without actually saving any files.

Syntax

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex,
Title, ButtonText)

expression is a variable that represents an Application object.

Parameters:

InitialFilename: Optional Variant Specifies the suggested file name. If this
argument is omitted, Microsoft Excel uses the active workbook's name.

FileFilter: Optional Variant A string specifying file filtering criteria.

FilterIndex: Optional Variant Specifies the index number of the default file
filtering criteria, from 1 to the number of filters specified in FileFilter.
If this argument is omitted or greater than the number of filters present,
the first file filter is used.

Title: Optional Variant Specifies the title of the dialog box. If this
argument is omitted, the default title is used.

I used this method because I wanted all the spreadsheets used on my team
saved in a particular way. So I created a dialog box (userform) that prompts
a user for information: name, month, week, center they are reviewing, etc.
All of that information is captured by the dialog box, put in a particular
order and assigned to a variable I created (IFN). Then that variable is used
by the method described above to save the spreadsheet using a specified file
name. The code for the procedure follows:

Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNweek As String 'File Name
Dim FNmonth As String 'File Name
Dim FNname As String 'File Name
Dim IFN As String

Month7Select = Month7.Value
MonthRSelect = MonthR.Value
WeekSelect = Week.Value
NameSelect = AName.Value
CenterSelect = Center.Value
Cells(1, 25) = Month7Select
Cells(1, 1) = MonthRSelect
Cells(2, 1) = WeekSelect
Cells(1, 2) = NameSelect
Cells(2, 2) = CenterSelect

If MonthRSelect = "January" Then FNmonth = "Jan"
If MonthRSelect = "February" Then FNmonth = "Feb"
If MonthRSelect = "March" Then FNmonth = "Mar"
If MonthRSelect = "April" Then FNmonth = "Apr"
If MonthRSelect = "May" Then FNmonth = "May"
If MonthRSelect = "June" Then FNmonth = "Jun"
If MonthRSelect = "July" Then FNmonth = "Jul"
If MonthRSelect = "August" Then FNmonth = "Aug"
If MonthRSelect = "September" Then FNmonth = "Sep"
If MonthRSelect = "October" Then FNmonth = "Oct"
If MonthRSelect = "November" Then FNmonth = "Nov"
If MonthRSelect = "December" Then FNmonth = "Dec"
If WeekSelect = "Week 1" Then FNweek = "wk1"
If WeekSelect = "Week 2" Then FNweek = "wk2"
If WeekSelect = "Week 3" Then FNweek = "wk3"
If WeekSelect = "Week 4" Then FNweek = "wk4"
If WeekSelect = "Week 5" Then FNweek = "wk5"
If NameSelect = "Bishop Minter" Then FNname = "bm"
If NameSelect = "Carlos Trespalacios" Then FNname = "ct"
If NameSelect = "Dennis Murphy" Then FNname = "dm"
If NameSelect = "Gary Hayden" Then FNname = "gh"
If NameSelect = "Gloria Montoya" Then FNname = "gm"
If NameSelect = "Kenneth Accomando" Then FNname = "ka"
If NameSelect = "Lisa Muttillo" Then FNname = "lm"
If NameSelect = "Lorraine Warburton" Then FNname = "lw"
If NameSelect = "Warner Langlois" Then FNname = "wl"

IFN = CenterSelect & " C&A PF " & FNmonth & " 09 " & FNweek & " " & FNname

Unload NotSoFast

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlOpenXMLWorkbookMacroEnabled

End Sub

Keep in mind that I originally created this code in Excel 2003 so when I
migrated to 2007 the following two lines of code needed to be changed from:

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Microsoft Office Excel Workbook(*.xls),*.xls", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlWorkbookNormal

to:

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN, _
FileFilter:="Excel Macro-Enabled Workbook(*.xlsm),*.xlsm", _
FilterIndex:=1, Title:="You Must Save Before You Proceed")

ActiveWorkbook.SaveAs AUserFile, xlOpenXMLWorkbookMacroEnabled

The reason I was getting the error message and not able to open workbooks
after I saved and closed them was because I only changed the Filefilter (from
"Microsoft Office Excel Workbook(*.xls),*.xls", to "Excel Macro-Enabled
Workbook(*.xlsm),*.xlsm") and not the format. So I was saving my workbook
using a 2007 file extension (.xlsm) but using a 2003 file format
(xlWorkbookNormal). Once I changed the file format (from xlWorkbookNormal
to xlOpenXMLWorkbookMacroEnabled) from a 2003 format to a 2007 format
everything was fine.

Hope this helps.