Macro to save an xml (Excel2007) file into an Excel97 format

Macro to save an xml (Excel2007) file into an Excel97 format
Hi, I created this macro (below) in order to let it run automatically over 
number of .xml files that have to be saved into another place(directory) but 
as Excel97 files.
(in fact I have a DOS script that selects all .xml files in a certain 
directory. Each selected file should be opened automatically and saved 
automatically into a subdirectory called xls, saving the file as an Excel97 
document (as you see below, I use the parameter FileFormat:=xlExcel8 _ to 
instruct VB to do so) ).
I you save the code into a file called PERSONAL.xls and put it into the 
directory ../XLSTART , excel will execute the macro right away when started.

MY PROBLEM : this macro worked with Excel2003 but soon we are going to 
upgrade to Excel2007! The macro stops when it tries to save the file (right 
after the MessageBox) and Excel crashes.

All help = welcome.
THnks.
Filip.

---


Option Explicit
 
 'The following function returns the filename without the extension from the 
file's full path:
Function FileNameNoExt(strPath As String) As String
    Dim strTemp As String
    strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
    FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
End Function
 
'the following function will get the path only (i.e. the folder) from the 
file's ful path:
Function FilePath(strPath As String) As String
    FilePath = Left$(strPath, InStrRev(strPath, "\"))
End Function

Sub auto_open()
       Application.Quit
End Sub

Sub auto_close()

   Dim NewFName As String
   Dim FullPathAs String

   NewFName = "xls\" & FileNameNoExt(ActiveWorkbook.FullName)
   FullPath= FilePath(ActiveWorkbook.FullName)

   MsgBox " NewFName: " & NewFName
   MsgBox " VolledigPad: " & VolledigPad

    ActiveWorkbook.SaveAs Filename:=FullPath+ "xls\" + NewFName, 
FileFormat:=xlExcel8 _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End Sub
date: Mon, 28 Apr 2008 06:47:00 -0700 author: Filip De Decker Filip De