Excel Crashes while saving embedded file thru VBA

[megahurtz]

Hi All,

I have a code that opens an embedded excel thru VBA, saves it on a user-specified location, closes the embedded file. Then the saved file will be opened and edited,
The problem that i'm facing is dat when i try to save the embedded sheet, excel crashes with no specified error. It simply crashes. I've no idea what to do. Please help....


My code is as shown below:

Set XlWbk = ThisWorkbook

Set oEmbFile = XlWbk.Sheets("Release Note Template").OLEObjects("Release Note Template")
oEmbFile.Verb Verb:=xlPrimary
Set XlRN = oEmbFile.Object

re_path2:
saveaspath = Application.GetSaveAsFilename(InitialFileName:=home, Title:="Save the Release Note as...", _
                    FileFilter:="Excel 2007 Files (*.xlsx), *.xlsx, Excel 97-2003 Files (*.xls), *.xls")
'Application.StatusBar = "Saving the Release Note..."
a = MsgBox("Save the Release Note on path " & saveaspath & "?", vbQuestion + vbYesNo)
If a = vbNo Then GoTo re_path2
'XlRN.SaveCopyAs saveaspath
XlRN.Activate
XlRN.SaveAs filename:=saveaspath, ReadOnlyRecommended:=False, CreateBackup:=False ', FileFormat:=xlNormal
XlRN.Close
Set oEmbFile = Nothing
Set XlRN = Nothing

Set XlRN = oExcel.Workbooks.Open(saveaspath)

XlWbk.Activate
wsRNT.Activate

The Excel crashes at point

XlRN.SaveCopyAs saveaspath

or

XlRN.SaveAs filename:=saveaspath...

Please guide. Thanks in advance...

 

[Jaafar Tribak]

The Object Property of the OleObject Class doesn't have a SaveAs Method.
You will need a workaround in order to save an embeeded object to disk maybe by copying it to the clipboard and then to disk.

 

[megahurtz]

Thanks Jaafar,

The workaround i implemented was :

XlRN.Activate
Activesheet.SaveCopyAs saveaspath

It worked!

 

[Jaafar Tribak]

Thanks Jaafar,

The workaround i implemented was :

XlRN.Activate
Activesheet.SaveCopyAs saveaspath

It worked!

Glad you got it working

 

[wigi]

Thanks Jaafar,

The workaround i implemented was :

XlRN.Activate
Activesheet.SaveCopyAs saveaspath

It worked!

Hmm

Maybe I do something wrong, but if I use this line of code in a saved one-sheet simple test file, it throws an error:

ActiveSheet.SaveCopyAs "C:\onesheet.xlsm"

Am I missing something here? SaveCopyAs applies to workbooks, no?

Error: object doesn't support this object or property.

 

[Jaafar Tribak]

Correct me anyone but I believe the Worksheet.SveAs was first introduced in Excel2007. Are you using excel 2003 or earlier ?

The one thing that I don't understand is that Worksheet.SaveAs saves the entire workbook and not the target worksheet as one would expect .

 

[wigi]

Correct me anyone but I believe the Worksheet.SveAs was first introduced in Excel2007. Are you using excel 2003 or earlier ?

I'm on Excel 2007. Maybe you meant Excel 2010?

 

[Jaafar Tribak]

I'm on Excel 2007. Maybe you meant Excel 2010?

No - I have just tested it on Excel 2007 and it works. No compile error. Maybe someone who knows can shed a light on this.

 

[megahurtz]

My apologies, but the actual workaround i'm using is

ActiveWorkbook.savesas.....

It was a typo saying

Activesheet.saveas...

 

[wigi]

My apologies, but the actual workaround i'm using is

ActiveWorkbook.savesas.....

It was a typo saying

Activesheet.saveas...

And then, also,

ActiveWorkbook.SaveCopyAs saveaspath

If yes, then I'm ok. And I don't know why Jaafar could get the other one to work. Maybe someone else could confirm if the version with a sheet also works.