VBA Add-In in MS Excel using a URL containing XML calling on Internet Explorer.

Question

VBA Add-In in MS Excel using a URL containing XML calling on Internet Explorer.

Delli gatti, Joseph 1

I got a new laptop, and now my custom VBA add-in doesn't work. I updated the new laptop; so, I think they've got to be using the same software and OS versions now. The only difference is that my old laptop used to have Internet Explorer on it (which was removed by IT several months ago). After IE removal, the add-in continued to function. But it's not working on the new laptop even after updates.

The VBA code produces a URL containing XML elements that are sent to the USPS Website.
The USPS Website provides back a page containing a response - also in XML format.
The VBA finds the appropriate XML tag and imports the result to a cell in my document.

The core to the VBA code is as follows:

    Set ie = CreateObject("InternetExplorer.Application")  
    ie.navigate URLAll  
While ie.ReadyState <> 4  
    DoEvents  
Wend  

Set ieDoc = ie.document  
ReturnData = ieDoc.activeElement.innerText  
iCnt = Len(ReturnData)

At this point, the VBA runs and appears to send the request correctly to Edge. But then, Edge returns the following error:

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<Error>
<Number>80040B19</Number>
<Description>XML Syntax Error: Please check the XML request to see if it can be parsed.(B)</Description>
<Source>USPSCOM::DoAuth</Source>
</Error>

I have validated the syntax of the URL.

http://production.shippingapis.com/ShippingAPI.dll?API=Verify&XML=  
<AddressValidateRequest USERID="My ID removed for security">  
<Address ID="0">  
<Address1>3474 S. 2300 E.</Address1>  
<Address2></Address2>  
<City>Salt Lake City</City>  
<State>UT</State>  
<Zip5></Zip5>  
<Zip4></Zip4>  
</Address>  
</AddressValidateRequest>

Anonymous

2022-07-22T02:20:40.293+00:00
Hi @Delli gatti, Joseph ,

Could you please share the details below?

Which Windows OS you are using and what exact version you are using?

You said IT remove the IE browser on your old/ new laptop. Did they uninstall it? disable it using the group policy? or block it using any other method?

You said, your code opens the MS Edge window and then shows the error. do you see any redirection happening after the IE got launched by your code? This code is not compatible to work with the Edge browser.

Could you please try to know from your IT department, what is the difference in the configuration regarding the IE on your old/ new laptop?

If possible for you then put the breakpoint in your VBA code and try to debug it line by line to see at what line it gets broken.

If you try to run the VBA code below, are you able to run it successfully? does it open the IE Window? Sub Automate_IE()
Dim URL As String Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://<Any_Website_here>" ' Replace the URL here...... IE.Navigate URL Do While IE.ReadyState = 4: DoEvents: Loop 'Do While Do Until IE.ReadyState = 4: DoEvents: Loop 'Do Until Set IE = Nothing End Sub

Please do let me know if you have questions.

Best Regards,
Delli gatti, Joseph 1 Reputation point

2022-07-22T06:25:28.84+00:00
My Windows version is as follows:

Edition Windows 10 Enterprise
Version 21H2
Installed on ‎7/‎13/‎2022
OS build 19044.1288

My version of Microsoft Edge is Version 103.0.1264.62 (Official build) (64-bit)

My version of Excel is Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20706) 64-bit

Internet Explorer was removed from my laptop via an update they pushed out to the company. If it was still on my computer, I couldn't see it or use it.

I'm not able to ask IT detailed questions.

Essentially, this is how the code works:

The VBA code runs from Excel when I click on my add-in.

Without opening any visible windows, it sends a url through IE.

The url is translated on the receiving end, and replies back in the form of an xml document.

The VBA code returns the contents of the document, extracts the parts of the XML that I need, and puts the value in a specified cell of my document.

I compared my VBA dictionaries on the old laptop to the new one. They appear to match.

I also tried setting a MsgBox containg the resulting url between the time it's compiled and when it's sent to this portion of the code. I checked the formatting, and it appears correct to me. I manually pasted it into Edge, and I get the same error as when I run the code.

When I run your code with my url, I get the following VBA error:

Run-time error '-2147023179 (800706b5)
Automation error
The interface is unknown.

This is the same error I get in my code currently.
Anonymous

2022-07-22T08:11:49.563+00:00
Hi @Delli gatti, Joseph ,

Thanks for sharing the information regarding the said issue.

For testing purposes,

Could you please try to open the VBA Editor. Go to Tools -> References.

Uncheck all the references that you had checked before.

Again add all the necessary references.

Click OK to close the References dialog.

Run your VBA code. See if it works.

Let us know about your test results.

Best Regards,
Deepak

Your answer

Anonymous

2022-07-22T02:20:40.293+00:00

Hi @Delli gatti, Joseph ,

Could you please share the details below?

Which Windows OS you are using and what exact version you are using?

You said IT remove the IE browser on your old/ new laptop. Did they uninstall it? disable it using the group policy? or block it using any other method?

You said, your code opens the MS Edge window and then shows the error. do you see any redirection happening after the IE got launched by your code? This code is not compatible to work with the Edge browser.

Could you please try to know from your IT department, what is the difference in the configuration regarding the IE on your old/ new laptop?

If possible for you then put the breakpoint in your VBA code and try to debug it line by line to see at what line it gets broken.

If you try to run the VBA code below, are you able to run it successfully? does it open the IE Window? Sub Automate_IE()
Dim URL As String Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True URL = "https://<Any_Website_here>" ' Replace the URL here...... IE.Navigate URL Do While IE.ReadyState = 4: DoEvents: Loop 'Do While Do Until IE.ReadyState = 4: DoEvents: Loop 'Do Until Set IE = Nothing End Sub

Please do let me know if you have questions.

Best Regards,
Delli gatti, Joseph 1 Reputation point

2022-07-22T06:25:28.84+00:00

My Windows version is as follows:

Edition Windows 10 Enterprise
Version 21H2
Installed on ‎7/‎13/‎2022
OS build 19044.1288

My version of Microsoft Edge is Version 103.0.1264.62 (Official build) (64-bit)

My version of Excel is Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20706) 64-bit

Internet Explorer was removed from my laptop via an update they pushed out to the company. If it was still on my computer, I couldn't see it or use it.

I'm not able to ask IT detailed questions.

Essentially, this is how the code works:

The VBA code runs from Excel when I click on my add-in.

Without opening any visible windows, it sends a url through IE.

The url is translated on the receiving end, and replies back in the form of an xml document.

The VBA code returns the contents of the document, extracts the parts of the XML that I need, and puts the value in a specified cell of my document.

I compared my VBA dictionaries on the old laptop to the new one. They appear to match.

I also tried setting a MsgBox containg the resulting url between the time it's compiled and when it's sent to this portion of the code. I checked the formatting, and it appears correct to me. I manually pasted it into Edge, and I get the same error as when I run the code.

When I run your code with my url, I get the following VBA error:

Run-time error '-2147023179 (800706b5)
Automation error
The interface is unknown.

This is the same error I get in my code currently.
Anonymous

2022-07-22T08:11:49.563+00:00

Hi @Delli gatti, Joseph ,

Thanks for sharing the information regarding the said issue.

For testing purposes,

Could you please try to open the VBA Editor. Go to Tools -> References.

Uncheck all the references that you had checked before.

Again add all the necessary references.

Click OK to close the References dialog.

Run your VBA code. See if it works.

Let us know about your test results.

Best Regards,
Deepak

Share via

VBA Add-In in MS Excel using a URL containing XML calling on Internet Explorer.

Your answer