Hi all

I try to catch an Excel Interop Event with my c# program, where i can save
the file the normal way as xls and in addition the same file as xml...so that
the user only once clicks save and chooses a location like c:\tmp\test.xls
while the xml file gets automatically saved into c:\tmp\xml\test.xml.

what event do i have to use to do this, and how can I do this? if somebody
could show my a little sample that would be very nice! is it possible at all?

thanks for your help
m.ahrens

The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

it still does not work... i changed the filename to c:\test3.xls

the funny thing is, that i don't get any error message. it just doesn't save
the file...
do you know why that is?

thanks
m.ahrens

"Sharad Naik" wrote:

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

i found the solution for the problem! had to create the file in the
filesystem...somehow excel .savecopyas doesn't create the file if it doesn't
already exist.

thanks for your help and have a nice day!
m.ahrens

"Sharad Naik" wrote:

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

i was happy to early...

it works if i save the workbook in the code where i start it. but if i call
the same save-methode in my event where i get my workbook as a
parameter...then it doesn't save it at all...shows no error...

do you know why that could be?

thanks
m.ahrens

"Sharad Naik" wrote:

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry, I have no further knowledge.
Hadn't touched 'C' since many years,
don't even have a compiler.
Sharad
"m.ahrens" wrote in message
...
i was happy to early...

it works if i save the workbook in the code where i start it. but if i
call
the same save-methode in my event where i get my workbook as a
parameter...then it doesn't save it at all...shows no error...

do you know why that could be?

thanks
m.ahrens

"Sharad Naik" wrote:

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as
xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the
BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is
beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

thanks for your help

i just saw that my special sign after the c disapeard...i'm programming in
c-sharp.

have a nice day
m.ahrens

"Sharad Naik" wrote:

Sorry, I have no further knowledge.
Hadn't touched 'C' since many years,
don't even have a compiler.
Sharad
"m.ahrens" wrote in message
...
i was happy to early...

it works if i save the workbook in the code where i start it. but if i
call
the same save-methode in my event where i get my workbook as a
parameter...then it doesn't save it at all...shows no error...

do you know why that could be?

thanks
m.ahrens

"Sharad Naik" wrote:

Shouldn't it be "c:\test3.xls" ?
(You have two back slashes, only 1 is needed and accepted.)

You have more parameters, I guess because, in Excel, it by default is
refering to the workbook in which the code is being written, in C with
reference to excel, you need to refer to a workbook.

I think SaveCopyAs ("c:\xlm\test3.xlm") should be engough to save it as
xlm
file. Try it if it really converts to xlm.

Sharad
"m.ahrens" wrote in message
...
thanks for your help...but i can't get it to work...

my code in the save-event looks like (i tryed to reduce it to a minimum
for
testing):

private void BeforeBookSave(Excel.Workbook Wb, bool SaveAsUI, ref bool
Cancel)
{
Cancel = true;
Wb.SaveCopyAs("c:\\test3.xls");
}

Wb.SaveCopyAs crashes but doesent give me an errormessage or
something...it
just doesen't save the file to "c:\\test3.xls"...

I also noticed that i have more parameters in the
BeforeBookSave-Methode
than you have...why is that??

what else to i have to do, that it works?

another question is: do i have to set a format so that the file is
beeing
saved as xml-file?

thanks
m.ahrens



"Sharad" wrote:


The event to catch is Workbook_BeforeSave, and in excel in this event
set Cancel = True , which will cancel the normal saving done by execl,
and then invoke your own method of saving

e.g. (In excel, Thisworkbook Class)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Cancel = True
'Your code to invoke your procedure in C
End Sub

Within Excel itself the code for what you want to do will be as under.
May be it will help you to do your code in C.
code in excel ( in Thisworkbook Class):

Public notAgain As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim xlsName As String, xlsPath As String, xlsFolder As String
Dim xlmName As String, xlmPath As String, xlmFolder As String
Dim fPos As Integer
Dim fs
If notAgain Then Exit Sub
notAgain = True
Cancel = True
xlsPath = Application.GetSaveAsFilename _
(InitialFileName:="", FileFilter:="Workbook (*.xls), (*.xls)")
If xlsPath = "False" Then 'user canceled saving
notAgain = False
Exit Sub
End If
ThisWorkbook.SaveAs xlsPath
xlsName = ThisWorkbook.Name
fPos = InStr(1, xlsPath, xlsName)
xlsFolder = Left(xlsPath, fPos - 1)
xlmFolder = xlsFolder & "xml\"
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(xlmFolder) Then
fs.CreateFolder (xlmFolder)
End If
fPos = InStr(1, xlsName, ".xls")
xlmName = Left(xlsName, fPos - 1) & ".xlm"
xlmPath = xlmFolder & xlmName
ThisWorkbook.SaveCopyAs xlmPath
notAgain = False
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!