F
Filip De Decker
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
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