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

LVL 1
BrendanKingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi, you might need to reference a specific workbook for the SaveAs to work. Try
Excel_obj.workbooks(1).Saveas(FileDest_Str & "\" & ServerName_Str)

which would perform SaveAs on the first element in the Workbooks collection.

Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
webavtCommented:
0
BrendanKingAuthor Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RobSampsonCommented:
>> 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.
0
BrendanKingAuthor Commented:
Yeah it works Well.

Thanks  for the extra information I was just about to ask if you new how to for overwrite.
0
RobSampsonCommented:
Ha ha.  Don't worry, I can't read minds....it's just something I've ran into before...

Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.