Solved Run a macro offline and email updated workbook

DerbyDad03 April 26, 2019 at 07:56:25

✔ Best Answer

I’m not a VBScript or Windows scheduler expert, so you’ll have to look into some of this stuff on your own. I just wanted to test a concept that came to mind and it seems to work. The way I imagine it could work would be to use the Windows Scheduler to run a .vbs file that would “open” the workbook and run the macro. I tried the following process and it seems to do what you are asking for: For simplicity, both of the following files are stored in C:\Users\my_user_name\ You could probably store them anywhere you want. File: test.xlsm Test.xlsm contains this formula in B1… (I’ll explain why later) =A1*5 …and this code in Module 1: Sub fillA1() ‘Increment A1 [a1] = [a1] + 1 ‘Did the sheet calculate? MsgBox [b1] End Sub File: test.vbs, which looks like this: Set objExcel = CreateObject("Excel.Application") objExcel.Application.Run "'C:\Users\my_user_name\test.xlsm'!Module1.fillA1" objExcel.DisplayAlerts = False objExcel.Application.ActiveWorkbook.Save objExcel.Application.Quit Set objExcel = Nothing In the command prompt window, I ran this command while test.xlsm was Closed: wscript test.vbs Each time I run that command I don’t see test.xlsm actually “open”, but I do see the MsgBox with the updated value of B1. That tells me that the macro is running, A1 is incrementing and the sheet is calculating the formula in B1. Therefore, I (dangerously) assume that the url-test UDF that you are using will update when the workbook is opened in the background. Obviously that is something you will need to test to make sure that it actually does. If this was my project, I imagine that my next step would be to use the Windows Scheduler to run test.vbs at whatever schedule fits my need. Obviously, your macro would be lot more extensive than mine. I imagine that your macro would perform the Copy/PasteValues that R2.3 suggested and even possibly email the file to your users. All that said, there is one thing that I really wonder about: You said that the function takes a considerable amount of time to test the url's. I have no idea what that will do to your system while the VBScript is running. Will the VBScript try to save the file way before the UDF has tested all url’s? If it does wait, will it freeze your system while it waits? These are the types of things that I don’t about VBScript so I’ll leave it up to you test and, hopefully, report back. If the VBScript method doesn't work efficiently, another method would be to put the Copy/Paste & Email code in a Workbook_Open macro in the ThisWorkbook module and then use the Windows Scheduler to actually open the workbook, which would cause the Copy/Paste & Email code to run. I still don't know how the "timing" of the UDF will impact the running of the code, but there may be a way to deal with that once we know that it is an issue. Only you have the ability to test any of this, so we'll wait for your reply. I hope that helps! How To Post Data or Code ---> Click Here Before Posting Data or VBA Code message edited by DerbyDad03

Thanks DerbyDad03. I'm about to sign off for the weekend, so I'll have a play with all of that on Monday and report back. Many thanks.

Razor2.3 April 26, 2019 at 16:36:05

So let's run through the options. 1) The macro that sparked this question works well enough as a one-off, but it doesn't scale. Partly due to network times, partly because each run starts up (the networking) half of a browser, pulls up a web page, and then closes the browser. There's a cost for that, but I don't know the exact cost. Easiest method is to just make the COM object static, so it sticks around while the worksheet calculation runs. Public Function TestURL(url As String) As Boolean Static request As Object If request Is Nothing Then Set request = CreateObject("WinHttp.WinHttpRequest.5.1") 'Not sure if this has to be set for each Open request request.SetAutoLogonPolicy 0 End If TestURL = False request.Open "HEAD", url request.Send TestURL = request.Status = 200 End Function 2) Do what we usually do here, and manually run VBA to fill in the results. Ideally done along with option 1. Sub RunChecks() Dim c as Range For Each c In Range("G2", [G:G]) If TestURL(c) Then c.Offset(0, 1) = "Valid" Else c.Offset(0, 1) = "Invalid" Next 'c End Sub 3) Have an external script modify / create this list and test the URLs. Usefulness depends on the frequency of this report. Viability depends on the underlying technological and political environment. If viable, I'd probably go this route. If just to avoid any warnings about embedded macros. That said, I couldn't do much to help here. I don't have an externally scriptable version of Excel, and you've seen how much of a hassle it is to get just 6 untested lines right. I don't know how this list is generated, nor its format (is it an .XLS? A .CSV?). Possibly the fastest runtime, as you could decide on using PowerShell, split the list up into 8 pieces, and do each piece concurrently. Or you could do as DerbyDad03 suggests, and run it as a scheduled task. How To Ask Questions The Smart Way

DerbyDad03 April 26, 2019 at 18:53:34

R2.3: Can you address my "question" about how VBScript handles external tasks that take a long time? In the previous thread SV said that the UDF you suggested "took about 12 minutes to check a sample of 232 URL" Referring to VBScript that I wrote, is it (the script) going to just sit around and wait until the UDF has checked each URL before it saves and closes the file? For some reason, I think not. I mean, how would it know that the Excel sheet is still busy doing stuff? Or would Excel itself prevent the Save from executing until the UDF and any subsequent macro has completed? That reminds of the old joke about a Thermos... You put hot stuff in a Thermos and it keeps the stuff hot. You put cold stuff in a Thermos and it keeps the stuff cold. How does it know? How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Razor2.3 April 26, 2019 at 19:03:38

VBScript just doesn't care. You do your work, you make your calls, when they're done, they're done. Until then, the script runs. As for how VBScript "knows" Excel is doing something, it doesn't. It's just making the calls, and waiting for the call to return. Also, consider setting objExcel.Visible = True when you're playing around with Excel via VBScript. How To Ask Questions The Smart Way message edited by Razor2.3

Razor2.3/DerbyDad03 Having seen DerbyDad03's suggestion about using the scheduler, I think that will work for me. Played around with a simple test file and all went to plan with the simple test file. And to answer Razor2.3's question about the list of links - it's a .xls file. But... I've been doing some testing/playing and I have a few issues... First I wanted to check whether the function recalculates when you reopen the workbook after the last save. I tested this by removing one of the documents from sharepoint that had previously been calculated as TRUE, then reopening the workbook. The function was still showing TRUE when it should have recalculated to FALSE. Having now read through https://superuser.com/questions/1261444/custom-formula-not-updating and https://stackoverflow.com/questions/5719650/user-defined-functions-not-recalculating I'm still unclear as to what I need to do to work around Excel's normal setup. My whole strategy relies upon the links being tested each time the workbook is opened so have to try and solve this somehow. Second When I run Sub RunChecks() from #3 above I get ByRef Argument Type Mismatch. When I debug, the c in If TestURL (c) Then is highlighted. I've googled and if I've understood correctly, it seems like VBA is expecting a variable of one type but is getting a different type. Is this because the function produces a boolean string, but 'c' is a range? Just trying to get my head round it... Here's what I've tried so far: 1) Change - Adding ByVal in front of 'url' in Public function TestURL(url As String) As Boolean Result - Compile error: Next without For 2) Change - Adding a second set of parentheses around 'c' in the Sub RunChecks() line If TestURL (c) Then Result - Next without For 3) Change - Changing Dim c As Range to Dim c (c As Range) As String Result - Compile error: Expected: list or separator or ) 4) Change - Changing If TestURL (c) Then to If TestURL(CStr(c)) Then as well as trying ...(CBool (c)) Then Result - Next without For Clearly I'm floundering around in the dark. I'll try and figure it out but if it's something quick to fix would you mind posting please? message edited by ScottV

Ok. I've managed to solve this myself (it seems...) Regarding the recalculation of the UDF - a bit of further googling threw up a solution that seems to work. By changing the very first line of Razor2.3's function from #3 above it seems to force a recalculation on opening. The line as was Public Function TestURL(url As String) As Boolean Changed to Public Function TestURL(url As String, Optional Volatile Parameter As Variant) As Boolean Then when I enter the formula with the function into the cell, I change it from =TestURL(J2) to =TestURL(J2, NOW()) If I test by adding and removing a sharepoint document and testing against the link, each time I open the worksheet, the result of the function updates accordingly. With regards to the issues I was having with the sub, I realised I didn't need it in that form. I just changed the macro so that when the scheduled task runs (using DerbyDad03's idea from #1 above) the vbs file opens the sheet with the links in, the function tests all the links, then the macro copies all the content and pastes values only into another workbook, saves it and closes it down. So the vbs file I have is: Set objExcel = CreateObject("Excel.Application") objExcel.Application.Run "'insert file name with links here'!Module1.macro name here" objExcel.DisplayAlerts = False objExcel.Application.ActiveWorkbook.Save objExcel.Application.Quit Set objExcel = Nothing Then in Module 1 in my worksheet I have: Public Function TestURL(url As String, Optional VolatileParameter As Variant) As Boolean Static request As Object If request Is Nothing Then Set request = CreateObject("WinHttp.WinHttpRequest.5.1") 'Not sure if this has to be set for each Open request request.SetAutoLogonPolicy 0 End If TestURL = False request.Open "HEAD", url request.Send TestURL = request.Status = 200 End Function Sub RunChecks() Dim wb As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False Set wb = Workbooks.Add ThisWorkbook.Sheets("Sheet1").Range("J:J, K:K").Copy wb.Sheets(1).[a1].PasteSpecial xlPasteValues wb.Sheets(1).Range("A:A, B:B").EntireColumn.AutoFit wb.SaveAs "insert name of file and location for paste values worksheet here" ActiveWorkbook.Close Application.DisplayAlerts = True End Sub No doubt there's probably improvements that can be made, but all is working so far. Thanks guys for your help. You're amazing to help newbies like me.

DerbyDad03 April 30, 2019 at 08:05:19

Great job. I'm glad it all worked out. Teamwork! How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Razor2.3 April 30, 2019 at 15:14:24

Actually should have been For Each c In Range("G2", [G:G].End(xlDown)) and there should have been an End If before the Next. Not sure how I managed to screw up copy/paste that badly, but there you go. That aside, I'm glad you decided you didn't need realtime status, as that'd require something a bit more than VBA. How To Ask Questions The Smart Way

Thanks both. Until next time... ;-)