Excel Interop Catch Event for Saving a file as xls and as xml?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
Sorry, I have no further knowledge.
Hadn't touched 'C' since many years,
don't even have a compiler.
Sharad
 
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
 
Back
Top