Scheduled Task to run macro to update links doesn't work

S_delight · Jun 24, 2013

Hello,

I have set up a scheduled task to update links in a workbook. This scheduled task just calls a macro in the workbook that updates external links. The problem I am having is that the scheduled task does not want to update the links. It only works when I run the macro directly from the Excel workbook. I have tried everything from changing the update links # (0,1,2,3) to changing the Startup Prompts in the Links section. I have no idea what I am doing wrong. Help please!!

This is the code used in the scheduled task:

[pre]

Code:

wscript.sleep 2000
Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm&#34;)
oExcelApp.Run (&#34;UpdateLinks&#34;)
oExcelApp.Quit

This is the VBA code for the "UpdateLinks" macro called above. This is stored in the actual workbook. 

ChDir &#34;O:PatrickDBSAllotmentsLMA&#34;
Workbooks.Open Filename:= _
&#34;O:PatrickDBSAllotmentsLMAWinter2012mission1.xlsm&#34;, _
UpdateLinks:=0
ChDir &#34;O:DSSTransport files to link to portal_2010&#34;
Workbooks.Open Filename:= _
&#34;O:DSSTransport files to link to portal_2010New Payload Report_S13.xlsm&#34;, _
UpdateLinks:=0
ActiveWorkbook.Save
Workbooks.Open Filename:= _
&#34;O:DSSTransport files to link to portal_2010Payload Report_W14.xlsm&#34;, _
UpdateLinks:=0
ActiveWorkbook.Save
Windows(&#34;Club_Med_Artist_2013-2014.xlsm&#34;).Activate
ActiveWorkbook.Save

[/pre]

SirJB7 · Jun 25, 2013

Hi, S_delight!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.

As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.

And about questions in general...

If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

And about this question in particular...

Where have you placed the code for the scheduled task calling procedure?

Regards!

S_delight · Jun 25, 2013

Hi,

Thank you for your reply. I've searched and nothing seems to come up regarding my specific issue.

The scheduled task calling procedure is in the Windows Task Scheduler. It is scheduled to run at a specific time every day. The scheduled task seems to run without a problem. However, it just does not want to update the links in the Excel workbook. I have to do it manually every time.

Thanks again for your help! No doubt, it will be something very simple and right in front of my nose...but I have tried everything I can think of or what I've found on the Internet.

SirJB7 · Jun 25, 2013

Hi, S_delight!

Would you mind posting the script file and the task scheduler action triggered?

BTW, how do you know it's run without any issues, have a log, creates any file or something alike? Please include the returned code for the last execution.

Regards!

S_delight · Jun 25, 2013

Hello!

This is the whole script:

[pre]

Code:

Dim oWshShell
Dim oAccessApp
Dim oExcelApp 

Set WshShell = WScript.CreateObject(&#34;WScript.Shell&#34;)
WshShell.Run &#34;C:AppwinPComPrivateCM.ws&#34;, 1

wscript.sleep 10000

&#39;**************************************************
&#39;****** Access Allotments    ******
&#39;************************************************** 

Set oAccessApp = CreateObject(&#34;Access.Application&#34;)
oAccessApp.automationsecurity=1
oAccessApp.Visible = False
oAccessApp.OpenCurrentDatabase (&#34;O:PatrickDBSAllotmentslmaWinter2012.mdb&#34;)
oAccessApp.Run (&#34;login&#34;)
oAccessApp.Run (&#34;collectInfo&#34;)
oAccessApp.Quit

&#39;**************************************************
&#39;****** Excel Allotments    ******
&#39;************************************************** 

wscript.sleep 2000

Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;O:PatrickDBSAllotmentslmaWinter2012mission1.xlsm&#34;)
oExcelApp.Run (&#34;DATA_inventory_update&#34;)
oExcelApp.Run (&#34;Save&#34;)
oExcelApp.Quit

wscript.sleep 2000

Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm&#34;)
oExcelApp.Run (&#34;UpdateLinks&#34;)
oExcelApp.Quit

wscript.sleep 2000

Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;cgassv0001revenueDSSRevenue files to link to portal_2010GO_vacations_2012.xlsm&#34;)
oExcelApp.Run (&#34;UpdateLinks&#34;)
oExcelApp.Quit

WshShell.AppActivate(&#34;Session A&#34;)
WshShell.SendKeys(&#34;%{F4}&#34;)
This portion of the code is what is not working properly:

wscript.sleep 2000

Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm&#34;)
oExcelApp.Run (&#34;UpdateLinks&#34;)
oExcelApp.Quit

wscript.sleep 2000

Set oExcelApp = CreateObject(&#34;Excel.Application&#34;)
oExcelApp.Visible = True
oExcelApp.Workbooks.Open (&#34;cgassv0001revenueDSSRevenue files to link to portal_2010GO_vacations_2012.xlsm&#34;)
oExcelApp.Run (&#34;UpdateLinks&#34;)
oExcelApp.Quit

[/pre]
It runs fine but when I open the Excel workbook, the links I have in there have not updated. When I run the "UpdateLinks" macro manually, the links do update. I know it if doesn't work because the update date in my excel file hasn't changed. I don't get any error message or anything. The links are just not being updated via above the script. I don't know if it's an issue with the VBA - that's the only code I have been playing with to see if any changes I make will work.

Thank you!

SirJB7 · Jun 25, 2013

Hi, S_delight!

I assume it's a .vbs file, but would you please post the task scheduler action configuration for this task and the returned code of last execution?

Regards!

EDITED

PS: If previous "UpdateLinks" for files:

"O

atrickDBSAllotmentslmaWinter2012mission1.xlsm"

"cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm"

"cgassv0001revenueDSSRevenue files to link to portal_2010GO_vacations_2012.xlsm"

worked fine and the error is happening for these files:

"cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm"

"cgassv0001revenueDSSRevenue files to link to portal_2010GO_vacations_2012.xlsm"

I'd first try moving these lines:

WshShell.AppActivate("Session A")

WshShell.SendKeys("%{F4}")

to the end of the script. Does this affect the update process? I don't know what's "Session A" application.

S_delight · Jun 25, 2013

Yes, it is a .vbs file. I'm not sure what you mean by task scheduler action configuration and the returned code. I don't know where to find that. The action just points to where the .vbs file is located. When we set it up, we just set up the trigger and the action.

The below refers to our reservation system (an AS400 system). This portion closes the session that is opened in the beginning of the code to update the Access files.

WshShell.AppActivate("Session A")

WshShell.SendKeys("%{F4}")

In a nutshell, this task opens up our reservation system, scrubs it for information and puts it in an Access database. We call the main Excel file (Winter2012mission1.xlsm), which is updated with the latest info from Access. From there the two other Excel files are opened and links are updated to the main Excel file & other reports we have. The updating of links is the least difficult task we are calling yet it is causing the most problems.

Thank you so much for your time!

SirJB7 · Jun 25, 2013

Hi, S_delight!

If you open the task scheduler (Start, Programs, Accessories, System Tools, Task Scheduler) a window with 4 panes will be opened. Maximize it, go to the top center pane, search for the related task, in the 6th column you'd find the return code of last execution. Now select the task clicking on it, right button, Properties. Then in the 3rd tab (Action) you'd find an entry like Start A Program, select it, and click on Edit button at bottom center. Confirm that Action is Start A Program, and please copy and paste "Program or Script" and Add Arguments -Optional- text boxes content.

Please confirm that the first 3 Excel updates are run and the last 2 not. If so, could you give a try to the alternative of moving the WshShell entries to the end of the script?

Regards!

S_delight · Jun 26, 2013

Hello again,

In Actions, it does say Start a Program.

The "Program or Script" is the following: "cgassv0001revenueDSSScheduled JobsMissions_Club_Med_Artists_2010.vbs"

There is nothing in Add Arguments.

When I go to the History tab (the 6th tab), there is nothing in there, which is strange. It says Number of Events: 0 and there is nothing in the box underneath (Level, Date & Time, etc.). I checked our other tasks and there is information in there so I don't know why nothing comes up for this task.

I did move the WshShell code to after the Access portion of the code (see below) and reran it - it still did not work. I watched it run and it goes through the whole sequence, opening up all three Excel documents called in the task. The data is updated, the links, however, still are not. Could it be something with the Excel VBA?

----------------------------------

Dim oWshShell

Dim oAccessApp

Dim oExcelApp

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "C:AppwinPComPrivateCM.ws", 1

wscript.sleep 10000

'**************************************************

'****** Access Allotments ******

'**************************************************

Set oAccessApp = CreateObject("Access.Application")

oAccessApp.automationsecurity=1

oAccessApp.Visible = False

oAccessApp.OpenCurrentDatabase ("O

atrickDBSAllotmentslmaWinter2012.mdb")

oAccessApp.Run ("login")

oAccessApp.Run ("collectInfo")

oAccessApp.Quit

WshShell.AppActivate("Session A")

WshShell.SendKeys("%{F4}")

wscript.sleep 2000

'**************************************************

'****** Excel Allotments ******

'**************************************************

wscript.sleep 2000

Set oExcelApp = CreateObject("Excel.Application")

oExcelApp.Visible = True

oExcelApp.Workbooks.Open ("O

atrickDBSAllotmentslmaWinter2012mission1.xlsm")

oExcelApp.Run ("DATA_inventory_update")

oExcelApp.Run ("Save")

oExcelApp.Quit

wscript.sleep 2000

Set oExcelApp = CreateObject("Excel.Application")

oExcelApp.Visible = True

oExcelApp.Workbooks.Open ("cgassv0001revenueDSSRevenue files to link to portal_2010Club_Med_Artist_2013-2014.xlsm")

oExcelApp.Run ("UpdateLinks")

oExcelApp.Quit

wscript.sleep 2000

Set oExcelApp = CreateObject("Excel.Application")

oExcelApp.Visible = True

oExcelApp.Workbooks.Open ("cgassv0001revenueDSSRevenue files to link to portal_2010GO_vacations_2012.xlsm")

oExcelApp.Run ("UpdateLinks")

oExcelApp.Quit

SirJB7 · Jun 26, 2013

Hi, S_delight!

Very strange but I don't think that VBA got corrupted, if so it'd shouldn't be updating links properly when manually ran.

So you confirm that?

a) The script has neither a last time run nor a return code (in the task scheduler).

b) The 1st 3 Excel files are updated (time stamp in file properties).

c) The last 2 Excel files aren't updated (idem).

d) The data is updated (yes for the 1st 3 and no for the last 2). Which data?

e) The links are not updated (all). Which links?

If all that happens could you specify exactly which differs from then scheduled process update vs. the manually ran process update? Regarding d) and e). Maybe you could upload a sample of those files?

Regards!

S_delight · Jun 27, 2013

Hi,

Yes I confirm that all the 5 items listed above are happening. For C - the 2 excel files are time stamped as of today because they are saved via the macro. However, the links are not updated in the actual files.

There is nothing that differs when running the scheduled process vs the manual one that I can see. I am calling the UpdateLinks macro in the scheduler vs pressing the macro button in Excel manually. The macro is running in the scheduler but it's just not updating properly.

I am happy to send you the report - how do I upload a file?

SirJB7 · Jun 27, 2013

Hi, S_delight!

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

S_delight · Jul 1, 2013

Hi there,

I've created a folder with the files on Dropbox for you. However, I need to provide an email address to share the folder with you. It does not just give me a general link. Do you have an email address I can send it to? Or is there another option?

I've uploaded the task scheduler code and the main workbook with the links that don't update. Do you need the other files it is linked to as well?

Let me know.

Thanks!

SirJB7 · Jul 2, 2013

Hi, S_delight!

About email addresses please check this:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903

It's my policy. If you want to post your email address here I'll get back to you and then I'll remove it from here unless you tell me to keep it.

Regards!

jassybun · Jul 2, 2013

S_Delight - In dropbox you move your file to "Public Folder" and when you right click - there should be an option to "Copy public link"..you paste that link here.

Scheduled Task to run macro to update links doesn't work

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

S_delight

New Member

SirJB7

Excel Rōnin

jassybun

Member

Scheduled Task to run macro to update links doesn&#39;t work

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

New Member

Excel Rōnin

Member

Scheduled Task to run macro to update links doesn't work