Solved Run a macro offline and email updated workbook

April 26, 2019 at 07:04:59
Specs: Windows 10
This question is related to a previous thread - (Sorry but I can't get the link to work. It's for thread ID 21577, but every time I click on it it opens this thread again...?)

I have a spreadsheet using a function that checks the validity of a long list of sharepoint URLs. The problem I have is that it is very slow and not feasible to use it in real time. I'm therefore wondering if it's possible to:

1) Have vba open the file at a scheduled time/date so that the function updates against all the URLs
2) Copy the URLs and the results of the function as values (using paste values) into a new workbook
3) Email the new workbook as an attachment to a recipient

The workbook with the URLs and the function in looks like this:

      Cols A-I         J          K
1 Irrelevant data     URLs     Link Test 
2 Irrelevant data     URL    =TestURL(J2)
3 Irrelevant data     URL    =TestURL(J3)
and so on....   

Here is the code for the function if helpful?

Public Function TestURL(url As String) As Boolean
  TestURL = False
  Dim request As Object: Set request = CreateObject("WinHttp.WinHttpRequest.5.1")
  request.Open "HEAD", url
  'WINHTTP_OPTION_AUTOLOGON_POLICY = WINHTTP_AUTOLOGON_SECURITY_LEVEL_LOW
  request.SetAutoLogonPolicy 0
  request.Send
  TestURL = request.Status = 200
End Function

message edited by ScottV


See More: Run a macro offline and email updated workbook

Reply ↓  Report •

#1
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


Reply ↓  Report •

#2
April 26, 2019 at 08:03:04
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.


Reply ↓  Report •

#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


Reply ↓  Report •

Related Solutions

#4
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


Reply ↓  Report •

#5
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


Reply ↓  Report •

#6
April 29, 2019 at 00:52:18
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


Reply ↓  Report •

#7
April 30, 2019 at 07:56:42
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.


Reply ↓  Report •

#8
April 30, 2019 at 08:05:19

Reply ↓  Report •

#9
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


Reply ↓  Report •

#10
May 1, 2019 at 00:10:51
Thanks both. Until next time... ;-)

Reply ↓  Report •

Ask Question