You are here:

Excel/Automate update Excel spreadsheet at specific time of day


QUESTION: Hello Bob. I hope you don't mind my writing to you, but I saw some of your other posts and thought you might be able to help me with a question concerning Excel. At least I hope you can...

We have a database which has all of our IT Inventory information in it, such as desktops and laptops discovered in our network, as well as other attributes of those discovered assets like IP address, CPU's, and the user of the asset. As we don't have the ability to actually add a "View" to the database, we have set up an Excel worksheet that will pull the information from the Inventory database and populate the Excel worksheet.

My question is this... Is there a way to automate, or schedule, when the worksheet will be updated with up-to-date information from the Inventory database? Our Inventory database gets updated each night at 2 a.m., and I would like to have the Excel spreadsheet automatically updated around 3 a.m., so we can then import that up-to-date information (from the Excel worksheet) into our Asset Manager application.

Is this possible using a Macro of some sort? I am not a programmer so I am not familiar with VBA or anything like that.

Thank you for any help you can give me. I certainly appreciate it.

Lee Chilcoat

ANSWER: Yes, it can be done with a macro, but I can't help without a LOT more information. Is the inventory info also an Excel file? Excel can be set to run a macro at any given time, but the program containing the macro would also have to be already open. Then when 3AM rolls around, the macro would kick in and do its thing. I know this doesn't really answer your question aside from yes, it can be done. Not sure of your next step tho.
Also, FYI, this information may be useful to you:

There's an online 6-hour VBA Class (2 evenings, 3 hrs each) class on June 4 & 6 from 8-11PM est. If you're interested in taking a live, online VBA class, check this out:go to On the left side, click the link "Virtual Classroom". On the right side, click "Virtual Classroom Schedule". Click the MIDDLE Dropdown, scroll to Microsoft Excel Visual Basic for Applications. Click "See your selection"click Course Outline (or register, etc.

)And/Or, see this announcement for in-person and more in-depth training (not only VBA):


There will be an Excel
User Conference June 26-28, 2013 in Las Vegas. There are 3 instructors: Bob Umlas(me)
(an Excel MVP), Tom Urtis (an Excel MVP), and Szilvia Juhasz (an Excel
trainer).  Please email me here if you think you may be interested and
I will follow up with you when the details are known, including hotel,
agenda, cost, etc. Please use subject of "EUC Interest" in the email.
Hope to see you there!

---------- FOLLOW-UP ----------

QUESTION: Hello Bob.  Thank you so much for getting back to me so quickly.  I really appreciate it!

The Inventory database is actually an SQL database, but I'm not sure what you mean when you said that "the program containing the macro would also have to be already open".  As things work right now, one of the database people wrote some kind of formula (or macro, I'm not really sure of the correct word here) in Excel that we can run and it populates the spreadsheet with the data from the Inventory database.  What we would like to do is to be able to have that formula (or macro) run automatically at a specific time of night so the Excel file would get updated from the Inventory database.

Thank you again!
Lee Chilcoat

The Excel file which contains the formulas which need updating must be open, and must be of type .xlsm (not .xlsx).
If you (or the programmer) has that file open, then he must press Alt/F11 (taking you to the programming environment), then doubleclick on "ThisWorkbook", and put in this code:

Private Sub Workbook_Open()
   Application.OnTime "3:00:00 AM", "update"
End Sub

Then he must use Insert/Module (from the pgmg environment (called the VBE)), and enter this code:

Sub Update()
   ActiveWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources(xlOLELinks)(1), Type:=xlExcelLinks
End Sub

Then, save the excel file & reopen it & leave it alone. At 3AM it will update the values by itself and save itself.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 All rights reserved.