2

I have a vbscript that runs an excel macro. Everything seems right, but it is not working as I had hoped. The task starts but then just continuously running without doing anything.

Let me show you everything I have... I don't get any errors, it just isn't running properly.

Task scheduler Timeline

  • Event 110 Task Triggered by user (Task Scheduler launched "{6569c7af-fed8-456b-8c8e-9d1653b8c15a}" instance of task "\Test" for user "tsee".
  • Event 319 Task engine received message to start task
  • Event 100 Task started - Task Scheduler started "{6569c7af-fed8-456b-8c8e-9d1653b8c15a}" instance of the "\Test" task for user "METRO\tsee".
  • Event 200 Action Started - Task Scheduler launched action "C:\Users\tsee\Desktop\vbsTest\runTest.vbs" in instance "{6569c7af-fed8-456b-8c8e-9d1653b8c15a}" of task "\Test".
  • Event 129 Created Task Process - Task Scheduler launch task "\Test" , instance "C:\Windows\System32\WScript.exe" with process ID 8964.

After that it just says "running" and doesn't execute anything.

My VBScript: (runTest.vbs)

Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("\\fileserver\homeshares\Tsee\My Documents\Programming\Task Scheduler\runTask.xlsm", 0, True)
xlApp.Run "runTaskTest"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

My excel Sheet and Macro: (runTask.xlsm) 

Sub runTaskTest()
    Dim erow As Long

    erow = Cells(Rows.Count, "A").End(xlUp).Row

    Cells(erow + 1, 1).FormulaR1C1 = "This test was successful : " & Now

    ThisWorkbook.Save
End Sub

Any help would be much appreciated. Thanks in advance!

Path network: enter image description here

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Adjit
  • 10,134
  • 12
  • 53
  • 98
  • 1. verify your network path 2. verify macro name –  Nov 26 '13 at 15:00
  • @mehow check post... verified... It also says desktop because I moved it actually onto my physical computer to see if that would change anything – Adjit Nov 26 '13 at 15:03
  • can you add debug points and see when the macro/script is starting to hang? –  Nov 26 '13 at 15:04
  • @mehow Downloading MSD now. It isn't happening in the excel macro. I have run that without any issues. – Adjit Nov 26 '13 at 15:06
  • try it like [**this**](http://stackoverflow.com/questions/4388879/vbscript-output-to-console) –  Nov 26 '13 at 15:10
  • @mehow this is going to sound stupid... but do they mean the cmd prompt? or do I run wscript.exe? – Adjit Nov 26 '13 at 15:15
  • or add `Application.DisplayAlerts = false` before `ThisWorkbook.Save` - try that too –  Nov 26 '13 at 15:17
  • @mehow what will the display alerts do? – Adjit Nov 26 '13 at 15:18
  • Excel may be saying you have made changes to the file do you want to save it? you, for some reason, may not be able to see that message box therefore wouldnt be able to click SAVE and proceed. What the DisplayAlerts does it blocks pop up messages from Excel –  Nov 26 '13 at 15:20
  • Ok progress!! So... from cmd I did `runTest.vbs` and then got an error that said `Cannot run the macro 'runTaskTest'. The macro may not be available in this workbook or all macros may be disabled.` – Adjit Nov 26 '13 at 15:21
  • its probably because you do not have all macros enabled by default. you can change that by either enabling all macros by default in Excel(not recommended) or adding a trusted location and placing your xlsm file in there –  Nov 26 '13 at 15:23
  • @mehow ok, so that is good news. Trying that now – Adjit Nov 26 '13 at 15:24
  • In Excel -> File -> Options -> Trust Center. Then Trust center settings and trusted locations. –  Nov 26 '13 at 15:26
  • @mehow didn't work... moved everything to this filepath: `C:\Users\tsee\Desktop\vbsTest` set it up as a trusted location. Fixed all of the path calls and got the same error – Adjit Nov 26 '13 at 15:33
  • so what if you comment out the `Run` like and execute your vbs –  Nov 26 '13 at 15:35
  • @mehow so `xlApp "runTaskTest"`? Got no error when I got rid of the whole line – Adjit Nov 26 '13 at 15:36
  • no temporarily comment out the entire line or remove it and try without it –  Nov 26 '13 at 15:37
  • @mehow no errors. Do you think I should maybe just change the script to when the file opens run? – Adjit Nov 26 '13 at 15:37
  • did you tick the box `allow trusted locations on my network` in trust centre in excel? –  Nov 26 '13 at 15:42
  • also go to Macro settings and `Enable all macros` temporarily –  Nov 26 '13 at 15:43
  • @mehow ...this is going to sound bad, but I already had that enabled. – Adjit Nov 26 '13 at 15:45
  • do a ctrl + del + alt and check if you dont have any Excel instances running in the background –  Nov 26 '13 at 15:47
  • @mehow got rid of all instances of excel and tried again... same error. – Adjit Nov 26 '13 at 15:49
  • oh thats a shame i think im running out of ideas... where is that excel macro placed? I hope you have added a module and not placed that under a sheet object module? Ie. right click in the VBA Project -> insert -> module –  Nov 26 '13 at 15:49
  • @mehow Never give up! lol I feel like hitting my head into a wall. The only thing I can think of is the vbscript isn't finding the macro... could it be because its an `xlsm` file or would that have no effect? Would I be able to do a workaround that says when this file is opened by the vbscript execute. – Adjit Nov 26 '13 at 15:52
  • 1
    please just verify that your excel macro is in the right place. It should be in a module and NOT in a Sheet Object Module. See my previous comment –  Nov 26 '13 at 15:53
  • @mehow well... this is awkward... No more errors... but it isn't printing the data to the sheet like I hoped, so I think its more of just a vba issue now – Adjit Nov 26 '13 at 16:02
  • @mehow so running the code from the module by itself prints the info to the sheet... but it doesn't do it when run through vbscript... any reason? – Adjit Nov 26 '13 at 16:10
  • yes because youre opening it read-only probably –  Nov 26 '13 at 16:29
  • Ive given you the answer it should work, please accept it if youre happy with it :) –  Nov 26 '13 at 16:31
  • @mehow absolutely! How should I be opening it so that it can be edited? – Adjit Nov 26 '13 at 16:34
  • when you run the `xlApp.WorkbooksOpen` there are 3 parameters , the third one indicates how to open the workbook - True means ReadOnly which is why the value didn't save in the workbook. Changing it to FALSE means read-write access –  Nov 26 '13 at 16:35

1 Answers1

1

Further to the comments

modify your VBS file

Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("\\fileserver\homeshares\Tsee\My Documents\Programming\Task Scheduler\runTask.xlsm", 0, False)

xlApp.DisplayAlerts = False
xlApp.Visible = False

xlApp.Run "runTaskTest"

xlBook.Saved = True
xlBook.Save

xlBook.Close
xlApp.Quit


Set xlBook = Nothing
Set xlApp = Nothing

and your macro

Sub runTaskTest()
    Dim erow As Long
    erow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    Sheets(1).Cells(erow + 1, 1).Value = "This test was successful : " & Now

    ThisWorkbook.Saved = True
    ThisWorkbook.Save
End Sub

and it should work

  • So I am getting an error the says `File in Use` when I try to open the file manually after running the script. I let it run for a minute or so... is something not working properly here? – Adjit Nov 26 '13 at 16:40
  • the Excel instance might be still hanging in the processes. Reboot your machine run the script and make sure the file is being closed. –  Nov 26 '13 at 16:41
  • It doesn't seem like the file is being closed. I just went into task manager and cleared out all instances of excel and was able to open it. Any reason for the hang or d you think a reboot is necessary? (fyi as a side note... try out `ctrl + shift + esc` instead of `ctrl + alt + del`) – Adjit Nov 26 '13 at 16:46
  • many reasons maybe your excel macro throws an error somewhere –  Nov 26 '13 at 16:47
  • 1
    found it... commented out `xlBook.Saved = True` and `xlBook.Save` from the vbs file and it ran perfectly fine. Thanks for all the help. Really, much appreciated. – Adjit Nov 26 '13 at 16:48
  • next step is getting it to work from the task scheduler... I think it is still hanging in there... running it now, we will see if it manages to complete... but it's already been a minute or two... should be done by now. – Adjit Nov 26 '13 at 16:51
  • run your macro in excel first and verify that it has no errors etc –  Nov 26 '13 at 16:52
  • been running it through the cmd prompt. Been working great. Then I try running it from Task Scheduler... its hanging. – Adjit Nov 26 '13 at 16:53
  • @metsales your original problem should now be solved. Consider asking a new question at this point. –  Nov 26 '13 at 16:55
  • Ok, I'll do that. Truthfully this was the original problem, but at least now we have narrowed down the possibilities. – Adjit Nov 26 '13 at 16:57
  • It's working now. I think I just had to restart the task scheduler. Cheers! – Adjit Nov 26 '13 at 17:21