Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about running macro from VBS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about running macro from VBS
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 --------------- Jacob Skaria "Bobbo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about running macro from VBS
For some reason the new additions didn't do anything.
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Bobbo" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about running macro from VBS
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 --------------- Jacob Skaria "Bobbo" wrote: For some reason the new additions didn't do anything. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Bobbo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running Total Question | Excel Worksheet Functions | |||
Running Sum question | Excel Worksheet Functions | |||
Running total question | Excel Worksheet Functions | |||
running maximum (newbie question) | Excel Discussion (Misc queries) | |||
Running Macros question | Excel Programming |