0

Trying to figure out the best way to kick off an excel macro at a certain time. I found a few suggestions, but I still have plenty of questions on the idea and was hoping you guys could provide some clarification.

What I am trying to do: I have live excel workbooks that are connected to my database. So at 11:30PM on the last day of every month I want to go into several excel workbooks (I have one for every vendor that we sell), enable the security and editing and refresh the connection so all of the information in the sheets is up to date. Lastly I would like to then save a copy of that file in a sub folder inside the current folder.

Run on time method:

Here is this run on time method that I found on ozgrid. My question is, I am assuming that excel would need to be running at the time in order to execute the method? If so what would be one way of opening up excel and refreshing a workbook to be saved?

Running excel on Windows Task Scheduler Windows Task Scheduler

Essentially this method is using an Excel Controller to run excel using VBS...Seems more promising than the run on time method but I am not very familiar with how an excel controller works or VBS, so any help or ideas on this one would be greatly appreciated.

Any input is greatly appreciated. If my ideas are way out in left field could you guys help point me in the right direction?

Thanks for all the help in advance!

pnuts
  • 58,317
  • 11
  • 87
  • 139
Adjit
  • 10,134
  • 12
  • 53
  • 98
  • 5
    I have covered about Task Scheduler [HERE](http://stackoverflow.com/questions/10211250/excel-vba-email-does-not-send-when-computer-is-locked) – Siddharth Rout Nov 25 '13 at 20:42
  • @SiddharthRout Just 1 quick question... Would you suggest using the vbscript to open all of the workbooks? or would it be possible to open up excel and kickoff a macro to open-refresh-save the workbooks one by one? – Adjit Nov 25 '13 at 20:51
  • You can opt in for either :) – Siddharth Rout Nov 25 '13 at 20:54
  • @SiddharthRout is there a big difference between the two options? or is it really the same thing just different ways of doing it? – Adjit Nov 25 '13 at 20:56
  • It's almost the same thing because the actual macro which does the work is already inside Excel. – Siddharth Rout Nov 25 '13 at 21:03
  • @SiddharthRout so I set up a quick test sheet with a macro that puts a time stamp and if the test passed... Running the vbs through the task scheduler as we speak... how long should this normally take because it has been a few minutes now? – Adjit Nov 25 '13 at 21:29
  • It depends on what time did you set the vbs to run at – Siddharth Rout Nov 25 '13 at 21:49
  • @SiddharthRout 11:30PM but I enabled running manually, so I was running it manually to test... Does it matter if I am using a web-server? This is the vbscript I am using `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` – Adjit Nov 25 '13 at 21:51
  • Umm, I have never tested it on a webserver :) – Siddharth Rout Nov 25 '13 at 21:52
  • @SiddharthRout the filepath is right I have been copying and pasting from the path bar. and sorry, meant file-server. I guess since I am leaving work now I will leave it and if it is still running tomorrow i'll post a more formal question on this :) – Adjit Nov 25 '13 at 21:56
  • @SiddharthRout posted a question with more details: http://stackoverflow.com/questions/20220430/task-scheduler-running-but-not-finishing-or-working-properly-vbscript – Adjit Nov 26 '13 at 14:54

1 Answers1

0

Turn the problem around. Create a workbook with normal Excel query tools and have it load the result set of the query when the workbook is opened (You don't specify a version, but see Refresh an external data connection for Excel 2013).

Then, either create a view in the database that contains the results you require, or to materialise the result of a view into a staging table that is then read by the workbook.

Users can then open the workbook whenever they require data, and the current results will be available. A parametrised version of the query could then access a history table to older periods.

This way all data is only prepared when required, and is always available without the scheduling complexity.

Pekka
  • 3,529
  • 27
  • 45