Excel VBA: Auto-Accept Certificates (Already Done in VBScript)

What VBA code would I need to auto accept digital certificates. At the link below it looks like someone possibly found a way to skip digital certificates all together in VBScript. I didn't know if there was a way to do the same in VBA. If this isn't possible, I am also interested in a way to automatically identify any certificate window that opens and somehow navigate it using VBA. (i.e., in effect automatically accepting it.)

https://social.technet.microsoft.com/Forums/systemcenter/en-US/31f6dba8-0dd3-417e-9236-a93cbca643e9/accept-all-ssltsl-certificates-in-vbscript?forum=operationsmanagermgmtpacks
ouestqueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
The same code will work in VBA. Although I don't think it's a smart idea to ignore the security certificates.
0
ouestqueAuthor Commented:
Hey MacroShadow. I inherited a file that contains VBA that pulls data from different websites.  Nonetheless, in order to pull that data, a certificate pops up asking if the user wants to accept. They run this file every hour and if they accidentally click don't accept, the data doesn't download and they think everything is broken. Therefore, I just want to import data from a reputable website without requirements from the user. (i.e., In this case without the user having to interact with a certificate correctly for everything to work.)

What would be the risks of automating security certificates in VBA temporarily for a specific purpose?
Also, do I need to make any general modifications for the linked VBA to work?
0
MacroShadowCommented:
What would be the risks of automating security certificates in VBA temporarily for a specific purpose?

I don't know and for that reason alone I'd be wary to do it.

Also, do I need to make any general modifications for the linked VBA to work?
If your'e using the same method (Msxml2.ServerXMLHTTP.6.0) to download the files, no modifications are necessary.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ouestqueAuthor Commented:
Hey MacroShadow. I have given the code a try, but it seems to have timed out. I get the following error: "Run-time error '-2147012894 (80072ee2)': The Operation Timed Out"

Will you take a look at my code and tell me what is wrong or what could be causing the problem?

Dim vGetHttp
Dim vUrlLink as string                            '<-----https link designated by the user and has a certificate for
Set vGetHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
vGetHttp.open "Post", vUrlLink, False
vGetHttp.SetOption(2) = 13056
vGetHttp.send                                           '<----This is where I get the runtime error
0
MacroShadowCommented:
Try setting the timeouts:
im vGetHttp
Dim vUrlLink as string                            '<-----https link designated by the user and has a certificate for
Set vGetHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
vGetHttp.setTimeouts(5 * 1000, 5 * 1000, 15 * 1000, 15 * 1000)
vGetHttp.open "Post", vUrlLink, False
vGetHttp.SetOption(2) = 13056
vGetHttp.send 

Open in new window

0
ouestqueAuthor Commented:
Hey MacroShadow,

I can't seem to get 'setTimeouts' to work. (i.e., it stays red) After a little research, it looks like some links make it sound like it might not be possible to use the method described in VBA. (See Below) If 'setTimeout' is doable, will maybe let me know what I am doing wrong with your syntax? Otherwise do you know of another alternative?

I am also willing to explore other options such as VBA that will somehow navigate the Certificate form that appears.

https://www.mrexcel.com/forum/excel-questions/827686-set-timeout-msxml2-xmlhttp-send-method.html
0
MacroShadowCommented:
Perhaps this will work:
call vGetHttp.setTimeouts(5 * 1000, 5 * 1000, 15 * 1000, 15 * 1000)
0
ouestqueAuthor Commented:
Hey MacroShadow,

Adding 'Call' does not seem to work either. The error message is: Run-time error '450': Wrong number of arguments or invalid property assignment. If this isn't a full proof way, maybe we can take a different approach...

When trying to .Open "Post" a certificate window opens up. Is there a way to navigate the Internet Explorer certificate window after it has opened?
0
MacroShadowCommented:
When trying to .Open "Post" a certificate window opens up. Is there a way to navigate the Internet Explorer certificate window after it has opened?
Not that I know of...

maybe we can take a different approach...
Sure. If the user has the certificate it is possible to automate the installation. Then there will be no certificate prompt.
0
ouestqueAuthor Commented:
cool. How do you automate the certificate installation?
0
MacroShadowCommented:
This is the command that will do it:
certmgr.exe -add -c "<cert-file>" -s -r localMachine root

Open in new window


It's fairly easy to run a bat file from VBA:
    Dim objShell As Object 
    Dim blnWaitOnReturn As Boolean
    Dim lngWindowStyle As Long
    Dim strCommand as Command

    Set objShell = CreateObject("WScript.Shell")
    blnWaitOnReturn = False
    lngWindowStyle = 1
    strCommand = certmgr.exe -add -c "<cert-file>" -s -r localMachine root

    'Run the desired shell command in command prompt.
    Call objShell.Run("cmd /k " & strCommand, lngWindowStyle, blnWaitOnReturn)

    'Cleanup
    Set objShell = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ouestqueAuthor Commented:
Very Cool!! Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.