asked on

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!

a0k0a7

ASKER

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

and still have the same error message.

ASKER CERTIFIED SOLUTION

Jan Karel Pieterse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

a0k0a7

ASKER

thanks for taking time to respond @jkpieterse, I don't know, how I can make sure the library it's installed?

a0k0a7

ASKER

I download it and install it now, Microsoft Core XML Services (MSXML) 6.0

a0k0a7

ASKER

Now I get automation error...

Jan Karel Pieterse

On which line of VBA code?

a0k0a7

ASKER

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")

Jan Karel Pieterse

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

a0k0a7

ASKER

I get a compile error Set XMLHTTP = New MSXML2.XMLHTTP40, also if I change it to Set XMLHTTP = New MSXML2.XMLHTTP60

Jan Karel Pieterse

To what precisely did you set a reference?

a0k0a7

ASKER

Set XMLHTTP = New MSXML2.XMLHTTP40 first

then tried Set XMLHTTP = New MSXML2.XMLHTTP60

a0k0a7

ASKER

I check the references thingy, sorry I misunderstood your comment. I am a novice with VBA.

a0k0a7

ASKER

How I do set a reference to the Microsoft XML library (Tools, references)?

Is it this?
Capture.JPG

Jan Karel Pieterse

Yes exactly!

a0k0a7

ASKER

I get a Run-time error :(

a0k0a7

ASKER

@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

a0k0a7

ASKER

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

on line

 sBody = tsIn.ReadAll

Open in new window

Jan Karel Pieterse

I'm sorry, I am unable to help with the Sharept I'm afraid!

a0k0a7

ASKER

@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.

Jan Karel Pieterse

Thanks. Good luck with figuring out the remainder of your problem!