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.
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 http://www.iil.com
. 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!
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"
Then he must use Insert/Module (from the pgmg environment (called the VBE)), and enter this code:
ActiveWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources(xlOLELinks)(1), Type:=xlExcelLinks
Then, save the excel file & reopen it & leave it alone. At 3AM it will update the values by itself and save itself.