schedule excel macro to run automatically

[rollingzep]

Hi,

I am trying to automate the download of a large file with Task Scheduler.

<CODE>

'Input Excel File's Full Path
ExcelFilePath = "P\pgovind\LCR\LCR_Test\LCR_INFO_Test_02.xlsm"

'Input Module/Macro name within the Excel File
MacroPath = "HQLA_MTG_Pools.HQLA_MTG_Pools"

'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
ExcelApp.Visible = True 'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
ExcelApp.DisplayAlerts = False



'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
ExcelApp.Run MacroPath

'Save Excel File (if applicable)
wb.Save

'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True

'Close Excel File
wb.Close

</CODE>

In the Task Scheduler, I set up the path Action

"C:\Windows\System32\cscript.exe"
and optional arguments

"\\P\pg\LCR\LCR_Test\Macro Launcher.vbs"

But it is not running when I tried with a scheduled time.

Is my macro path correct? MacroPath = "HQLA_MTG_Pools.HQLA_MTG_Pools. The module name and macro are the same.
Or is the space creating issues in Macro Launcher.vbs

 

[rollingzep]

Hi,

I am trying to automate the download of a large file with Task Scheduler.

<CODE>

'Input Excel File's Full Path
ExcelFilePath = "P\pgovind\LCR\LCR_Test\LCR_INFO_Test_02.xlsm"

'Input Module/Macro name within the Excel File
MacroPath = "HQLA_MTG_Pools.HQLA_MTG_Pools"

'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
ExcelApp.Visible = True 'or "False"

'Prevent any App Launch Alerts (ie Update External Links)
ExcelApp.DisplayAlerts = False



'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
ExcelApp.Run MacroPath

'Save Excel File (if applicable)
wb.Save

'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True

'Close Excel File
wb.Close

</CODE>

In the Task Scheduler, I set up the path Action

"C:\Windows\System32\cscript.exe"
and optional arguments

"\\P\pg\LCR\LCR_Test\Macro Launcher.vbs"

But it is not running when I tried with a scheduled time.

Is my macro path correct? MacroPath = "HQLA_MTG_Pools.HQLA_MTG_Pools. The module name and macro are the same.
Or is the space creating issues in Macro Launcher.vbs

Do I need to declare the wb, ExcelFilePath , MacroPath and ExcelApp in the .VBS script using Dim?