asked on

Excel.application saveas Workbook

Hi Guys.
I have a little VBscript that is gathering server info and exporting to excel Spreed sheet.

I want to save the Excel Spreed sheet as the name of the Server.

But I can not seam to get the wookbooks.saveas feature to work.

If I use excel.application.save it only save as book1.xls.
If I use excel.appplication.workbooks.saveas I get object doesn't support this property or method.

Hope someone knows the solution.

dim excel_obj
Sub SaveExcel_Fnc(FileDest_Str,ServerName_Str)
	Excel_obj.workbooks.Saveas(FileDest_Str & "\" & ServerName_Str)
	Excel_obj.quit
End Sub
 
Sub CreateExcel_Fnc
'Create Excel Spreedsheet
	Set Excel_obj = CreateObject("Excel.Application")
	Excel_obj.Visible = True
	Excel_obj.Workbooks.Add
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

webavt

This VB reference from Microsoft might help...

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(VS.80).aspx

BrendanKing

ASKER

Can't believe I could not find anything on MSDN with that information.

The all Said just workbooks.saveas.

But workbooks(1).saveas works.

Thanks.

RobSampson

>> But workbooks(1).saveas works.

Great! So does that save the workbook correctly?

If the filename already exists, you'll also need to use

Excel_obj.DisplayAlerts = False
Excel_obj.workbooks(1).Saveas(FileDest_Str & "\" & ServerName_Str)
Excel_obj.DisplayAlerts = True

so that it automatically overwrites the file.

Regards,

Rob.

BrendanKing

ASKER

Yeah it works Well.

Thanks for the extra information I was just about to ask if you new how to for overwrite.

RobSampson

Ha ha. Don't worry, I can't read minds....it's just something I've ran into before...

Rob.