Sleeper: How to make Excel Start itself with a VBA Macro

[Dinob661]

Hello All!

I am trying to find a way to write a macro, that will make excel open at a specific time by itself. Is this possible? (I realize this is almost an impossible task because how can you tell yourself to wake up when your asleep? you can't... but who knows maybe there's a macro out there that I haven't thought of! lol)

I already know about the task scheduler program, and I currently use it. But I am going to be putting my macros on a server, so task scheduler will not be available for me to use (The goal is to have the macro's run without my computer being involved at all).

Thank you,

dinob661

[Zack Barresse]

Take a look at Windows Scheduler. You can set an app to run at a certain time. The only trick is if Excel is already running then you'll open two instances. If you want to cater to that you can write a VBS file (written in notepad but saved with a ".vbs" extension), which can look to see if Excel is running, otherwise run it. Here is an example...

Dim XL
On Error Resume Next
Set XL = GetObject(, "Excel.Application")
If XL Is Nothing Then Set XL = CreateObject("Excel.Application")
On Error GoTo 0

HTH

[Dinob661]

Thank you for your response Zack!

Your post has me down the right path I believe. I used part of the code from your post and part of another I found from searching and I came up with this:

Set xl = CreateObject("Excel.application")
xl.Application.Workbooks.Open "F:\Automatic Reports (13)\Spend Auto.xlsm"
xl.Application.Visible = True

This code addresses one half of my problem. It gets Excel to open up by itself. Now I need to address the other half which is being able to set a time for it to do this. I saw you mentioned Windows Scheduler and I did some research, I found a code that could potentially solve my problem but I can't make it work. (I am moderately fluent with VBA but know hardly anything about VBS lol). Anyway here is the code I found

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objNewJob = objWMIService.Get("Win32_ScheduledJob")
errJobCreated = objNewJob.Create _
    ("Notepad.exe", "********91000.000000-420", _
        True , 1 OR 4 OR 16, , , JobID) 
Wscript.Echo errJobCreated

What this code is supposed to do is open notepad every monday, wednesday, friday at 910am using windows scheduler. How can I tailor this code to work with my code?


Thank you for any help you can provide!

[Paul_Hossler]

But I am going to be putting my macros on a server, so task scheduler will not be available for me to use (The goal is to have the macro's run without my computer being involved at all).

I thought that you were putting the XLSM on a server, and didn't want your PC involved. Don't those approaches all run on the PC?

Wouldn't the server have to have a UNIX-like cron equivalent?

I had a XLSM with a Workbook_Open event automatically launch at the PC level using Task Scheduler. To avoid macro prompts, I only needed to make the folder a trusted location.

Paul

[Dinob661]

No you're right,

I am trying to do it without my PC being involved. I guess my thought process is I am trying to find something, anything that can kick-start the excel sheet to open cause once it does, my macro is set up to do the rest. I just need that catalyst. I was thinking maybe the VBS coding could possible be that kick-starter... lol

[snb]

I'd prefer:

 With getObject("F:\Automatic Reports (13)\Spend Auto.xlsm")
   .Application.Visible = True 
 end with