Link to home
Create AccountLog in
Avatar of a0k0a7
a0k0a7Flag for United States of America

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!
Avatar of a0k0a7
a0k0a7
Flag of United States of America image

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
Avatar of Jan Karel Pieterse
Jan Karel Pieterse
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of a0k0a7

ASKER

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

ASKER

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

ASKER

Now I get automation error...

User generated image
On which line of VBA code?
Avatar of 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")
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
Avatar of a0k0a7

ASKER

I get a compile error Set XMLHTTP = New MSXML2.XMLHTTP40, also if I change it to Set XMLHTTP = New MSXML2.XMLHTTP60
To what precisely did you set a reference?
Avatar of a0k0a7

ASKER

Set XMLHTTP = New MSXML2.XMLHTTP40 first

then tried Set XMLHTTP = New MSXML2.XMLHTTP60
Avatar of a0k0a7

ASKER

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

ASKER

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

Is it this?
Capture.JPG
Yes exactly!
Avatar of a0k0a7

ASKER

I get a Run-time error :(User generated image
Avatar of 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

Avatar of 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 User generated image on line
 sBody = tsIn.ReadAll

Open in new window

I'm sorry, I am unable to help with the Sharept I'm afraid!
Avatar of 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.
Thanks. Good luck with figuring out the remainder of your problem!