Question about running macro from VBS

[Bobbo]

I am using the following script that will allow me to open up excel and run a
macro that is in it. It works fine but what I would like is that when it is
done, instead of asking me if I would like to just save the workbook It would
save the workbook with the data in cell B2 as the file name with the date
appended to the name in the same directory as the original workbook.

Here is what I am using right now. WhoMadeComments is the workbook name and
Get_Data is the macro

Set objXL = CreateObject("Excel.Application")
on error resume next

With objXL
.Workbooks.Open ("D:\Documents and
Settings\user\Desktop\testing\WhoMadeComments.xls")
.Run "WhoMadeComments.xls!Get_Data"
.Quit
End With
Set objXL = Nothing

 

[Jacob Skaria]

Try the below

With objXL
.Workbooks.Open ("D:\Documents and
Settings\user\Desktop\testing\WhoMadeComments.xls")
.Run "WhoMadeComments.xls!Get_Data"
.SaveAs .ThisWorkbook.Path & "\" & .Range("B1").Text & _
Format(Date, "dd-mm-yyyy") & ".xls"
.Quit
End With


If this post helps click Yes

 

[Bobbo]

For some reason the new additions didn't do anything.

 

[Jacob Skaria]

Hi Bobbo

There were few errors in my previous post..Try the below which uses a
workbook object. I have tried this with a dummy file. Test and feedback...In
case you have any probs; remark the resume next and place msgbox after each
line and idendity where it is returning an error..

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open ("D:\Documents and
Settings\user\Desktop\testing\WhoMadeComments.xls")

On error resume next

objXL.Run "WhoMadeComments.xls!Get_Data"
objWB.SaveCopyAs objWB.Path & "\" & objWB.ActiveSheet.Range("B1") & ".xls"
objXL.DisplayAlerts = False
objXL.Quit

Set objXL = Nothing
Set objWB = Nothing
Msgbox "Done"

If this post helps click Yes