Run-time error '429' ActiveX component can't create object

The following code purpose is to upload files to a sharepoint library, I run it from Excel using a form control, prior to it I run using an ActiveX button.

UserName = "myusername@sharepoint.com"
    pw = "mypassword"
    SharePointURL = "\\xyz.sharepoint.com@SSL\DavWWWRoot\sites\uat\a1docsuat" & "\"
    Set LobjXML = CreateObject("Microsoft.XMLHTTP")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("c:/vba2sharepoint/")
    For Each f In fldr.Files
        sharepointFileName = SharePointURL & f.Name
        'If sharepointFileName Like "*.txt" Then
            Set tsIn = f.OpenAsTextStream
            sBody = tsIn.ReadAll
            tsIn.Close
            Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
            xmlhttp.Open "PUT", sharepointFileName, False, UserName, pw
            xmlhttp.Send sBody
        'End If
    Next f

Open in new window


I get the message; Run-time error '429' ActiveX component can't create object
     Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")


Please advise, thanks!
LVL 8
a0k0a7Asked:
Who is Participating?
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.

a0k0a7Author Commented:
I tried with sharepointUrl = "https://xyz.sharepoint.com/sites/uat/_layouts/15/start.aspx#/a1docsuat/"

and still have the same error message.
0
jkpieterseCommented:
Is that library installed on your system?
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
a0k0a7Author Commented:
thanks for taking time to respond @jkpieterse, I don't know, how I can make sure the library it's installed?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

a0k0a7Author Commented:
I download it and install it now, Microsoft Core XML Services (MSXML) 6.0
0
a0k0a7Author Commented:
Now I get automation error...

automation error
0
jkpieterseCommented:
On which line of VBA code?
0
a0k0a7Author Commented:
It doesn't say, I changed the following line

from: Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
to:      Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
0
jkpieterseCommented:
Try setting a reference to the Microsoft XML library (Tools, references) and then change this code:

            Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")

to:

            Set XMLHTTP = New MSXML2.XMLHTTP40
0
a0k0a7Author Commented:
I get a compile error Set XMLHTTP = New MSXML2.XMLHTTP40, also if I change it to Set XMLHTTP = New MSXML2.XMLHTTP60
0
jkpieterseCommented:
To what precisely did you set a reference?
0
a0k0a7Author Commented:
Set XMLHTTP = New MSXML2.XMLHTTP40 first

then tried Set XMLHTTP = New MSXML2.XMLHTTP60
0
a0k0a7Author Commented:
I check the references thingy, sorry I misunderstood your comment. I am a novice with VBA.
0
a0k0a7Author Commented:
How I do set a reference to the Microsoft XML library (Tools, references)?

Is it this?
Capture.JPG
0
jkpieterseCommented:
Yes exactly!
0
a0k0a7Author Commented:
I get a Run-time error :(run time error
0
a0k0a7Author Commented:
@jkpieterse After changing the sharepointURL variable, I don't get any error but the files are not upload it, any ideas?

Public Sub CopyToSharePoint()

UserName = "myusername@sharepoint.com"
    pw = "mypassword"
    sharepointUrl = "https://xyz.sharepoint.com/sites/uat/_layouts/15/start.aspx#/a1docsuat/"
    Set LobjXML = CreateObject("Microsoft.XMLHTTP")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("c:\vba2sharepoint\")
    For Each f In fldr.Files
        sharepointFileName = sharepointUrl & f.Name
        'If sharepointFileName Like "*.txt" Then
            Set tsIn = f.OpenAsTextStream
            sBody = tsIn.ReadAll
            tsIn.Close
            'Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
            Set xmlhttp = New MSXML2.XMLHTTP60
            xmlhttp.Open "PUT", sharepointFileName, False, UserName, pw
            xmlhttp.Send sBody
        'End If
    Next f

End Sub

Open in new window

0
a0k0a7Author Commented:
I am trying to upload a PDF's files, if I put a text file on the c:/vba2sharepoint/ folder location I get an error input past end of file on line
 sBody = tsIn.ReadAll

Open in new window

0
jkpieterseCommented:
I'm sorry, I am unable to help with the Sharept I'm afraid!
0
a0k0a7Author Commented:
@jkpieterse thanks for trying, I feel like I am close to get it working, I will give you assistance points just to help me figure out the issue that I didn't have the XML library installed on my system.
0
jkpieterseCommented:
Thanks. Good luck with figuring out the remainder of your problem!
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
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.