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 recipientThe 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 Functionmessage edited by ScottV
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 SubFile: 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 = NothingIn the command prompt window, I ran this command while test.xlsm was Closed:
wscript test.vbsEach 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.
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 Function2) 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 Sub3) 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.
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
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) Thenis 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 BooleanResult - Compile error: Next without For2) Change - Adding a second set of parentheses around 'c' in the Sub RunChecks() line
If TestURL (c) ThenResult - Next without For3) Change - Changing
Dim c As RangetoDim c (c As Range) As StringResult - Compile error: Expected: list or separator or )4) Change - Changing
If TestURL (c) ThentoIf TestURL(CStr(c)) Thenas well as trying...(CBool (c)) ThenResult - 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 BooleanChanged to
Public Function TestURL(url As String, Optional Volatile Parameter As Variant) As BooleanThen 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 = NothingThen 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 SubNo 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.
Great job. I'm glad it all worked out. Teamwork!
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
Actually should have been For Each c In Range("G2", [G:G].End(xlDown))and there should have been anEnd Ifbefore 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.